sql - 想要从 sql server 中的表中查找名称和最高分数得分主题

Question

您好，我有一个表变量，我想找到每个学生的名字以及最高分科目。

declare @test1 table (name varchar(50), english int, math int, science int)

insert into @test1(name, english, math, science)
select 'A', 50, 90, 70
union all 
select 'B', 60, 80, 65
union all 
select 'C' , 80,65, 70
union all 
select 'D', 70, 75, 89

如果有人能帮助我，那将不胜感激。

Answer 1

这里有一个技巧可以做到这一点

SELECT TOP 1 WITH ties NAME,
                       sub_name,
                       mark
FROM   @test1
       CROSS apply (VALUES(english,'english'),
                          (math,'math'),
                          (science,'science'))tc(mark, sub_name)
ORDER  BY Row_number()OVER(partition BY NAME ORDER BY mark DESC) 

或者使用丑陋的CASE语句，但这会比Cross Apply

表现得更好

SELECT NAME,
       sub_name,
       CASE sub_name
         WHEN 'english' THEN english
         WHEN 'math' THEN math
         ELSE science
       END mark
FROM   (SELECT *,
               CASE
                 WHEN english > math AND english > science THEN 'english'
                 WHEN english < math AND math > science THEN 'math'
                 ELSE 'science'
               END AS sub_name
        FROM   @test1) a