如何从sql -server-2005中选择最大行值 [英] How to select max row value from sql -server-2005

问题描述

大家好，

我有这样的表格数据，

名称m1 m2 m3 m4
-----------------

N1 34 36 37 58
N2 35 31 45 67
N3 34 65 37 34
N4 34 78 09 58
N5 34 47 0 18
N6 89 36 56 60


我需要结果为

名称最高分数
---------------
N1 58
N2 67
N3 65
N4 78
N5 47
N6 89

如何在sql -server 2005

Hi All,

I have a table data like this,

Name m1 m2 m3 m4
-----------------

N1 34 36 37 58
N2 35 31 45 67
N3 34 65 37 34
N4 34 78 09 58
N5 34 47 0 18
N6 89 36 56 60


I need the result as

Name Maximum Mark
---------------
N1 58
N2 67
N3 65
N4 78
N5 47
N6 89

How to do this in sql -server 2005

推荐答案

中执行此操作.您可以尝试sql子查询.
这是一个例子

You may try sql subquery.
here is an example

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

原始表是

original table is

Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

这可能不是最佳方法，但它会提供您想要的输出....

This may not be the optimim way but it gives the output you want....

select nname, max(M.MaximumMark) from (
select nname, m1 as MaximumMark from test_ union
select nname, m2 as MaximumMark from test_ union
select nname, m3 as MaximumMark from test_ union
select nname, m4 as MaximumMark from test_) AS M
GROUP BY M.nname

这是另一个

Here is another one

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)

insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35, 31, 45, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34, 65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34, 78, 09, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34, 47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89, 36, 56, 60)

-- 1. [Name] is unique
select  [Name],
        case
            when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
            when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
            when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
            else [m4]
        end as MaximumMark
from    #temp

-- 2. [Name] is NOT unique
select  [Name],
        max(
            case
                when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
                when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
                when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
                else [m4]
            end
            ) as MaximumMark
from    #temp
group
by      [Name]

这篇关于如何从sql -server-2005中选择最大行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！