[MSSQL]一道合并成绩最高科目的解决方法

昨天在群里一位网友抛出这样的问题：

name   语文    数学     英语
张三    75    90       85
李四    80     85       85
获得表,查询每个 name 成绩最高的学科， 若有相同，并列
name    成绩    科目
张三    90      数学
李四    85      数学，英语

我的方案：

CREATE TABLE [dbo].[ChengJi2]([姓名] [nchar](10) NULL,[成绩] [int] NULL,[科目] nchar(100) NULL,) ON [PRIMARY]-------------------------------------Create Function [dbo].[Getkemu](@姓名 char(10)=null)returns  varchar(8000)asbegin      declare   @r   varchar(8000)       set   @r= ' '       select  @r=@r+ ', '+ rtrim(CAST (科目 as varchar))   from ChengJi2 where 姓名=@姓名      return   stuff(@r,1,2, ' ')end------------------------------------Insert into ChengJi2(姓名,成绩,科目) SELECT B.*  FROM (select 姓名,MAX (成绩) 成绩  from(SELECT     姓名, 语文 as 成绩,'语文' 科目FROM         ChengJi union allSELECT     姓名, 数学 as 成绩,'数学' 科目FROM         ChengJi union allSELECT     姓名, 英语 as 成绩,'英语' 科目FROM         ChengJi ) Tgroup by 姓名) A INNER JOIN         (select 姓名,MAX (成绩) 成绩,科目  from(SELECT     姓名, 语文 as 成绩,'语文' 科目FROM         ChengJi union allSELECT     姓名, 数学 as 成绩,'数学' 科目FROM         ChengJi union allSELECT     姓名, 英语 as 成绩,'英语' 科目FROM         ChengJi ) Tgroup by 姓名,科目) B ON A.姓名=B.姓名 and A.成绩=B.成绩-------------------------------------------------------------select 姓名,成绩,dbo.Getkemu(姓名) as 科目 from ChengJi2 group by 姓名, 成绩

网友一的方案：

select * into #tbfrom(select '张三' as name,60 as 语文,70 as 数学,80 as 英语unionselect '李四' as name,90 as 语文,70 as 数学,90 as 英语unionselect '王武' as name,80 as 语文,80 as 数学,80 as 英语) a----------------select name,max(成绩) as 成绩, (select case when 语文=MAX(成绩) then '语文,' else '' end+         case when 数学=MAX(成绩) then '数学,' else '' end+         case when 英语=MAX(成绩) then '英语' else '' end from #tb ab where ab.name=a.name) as 科目from(select name,语文 as 成绩,'语文' 科目from #tbunion allselect name,数学 as 成绩,'数学' 科目from #tbunion allselect name,英语 as 成绩,'英语' 科目from #tb) a group by a.name--------------------drop table #tb

网友二的方案：

create table #tmp(id int primary key,[name] varchar(255),语文 int,数学 int,英语 int);insert into #tmp values(1, '张三', 75, 90, 85);insert into #tmp values(2, '李四', 80, 85, 85);withtree as(select [name], 分数, 科目  from #tmpunpivot(分数 for 科目 in (语文, 数学, 英语))as unpvt),maxTree as(select * from tree t1 where 分数 >= (select max(分数) from tree t2 where t1.name = t2.name))select name, 分数, 科目 = stuff((select ',' + 科目 from maxTree t1 where t1.name = t2.name for xml path('')), 1, 1, '')from maxTree t2group by name, 分数drop table #tmp

总结：

如果单纯作为面试的解决方案，我的方法可行。

如果用函数可能面临不同数据库的语法的限制。用函数会导致效率比较低下。

网友一给出了比较通用效率高的方案，一开始我也是这个思路，但case when 掌握的不熟练。

网友二是我的方案的改进版