[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) as begin 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 all SELECT 姓名, 数学 as 成绩,'数学' 科目 FROM ChengJi union all SELECT 姓名, 英语 as 成绩,'英语' 科目 FROM ChengJi ) T group by 姓名) A INNER JOIN ( select 姓名,MAX (成绩) 成绩,科目 from( SELECT 姓名, 语文 as 成绩,'语文' 科目 FROM ChengJi union all SELECT 姓名, 数学 as 成绩,'数学' 科目 FROM ChengJi union all SELECT 姓名, 英语 as 成绩,'英语' 科目 FROM ChengJi ) T group by 姓名,科目) B ON A.姓名=B.姓名 and A.成绩=B.成绩 ------------------------------------------------------------- select 姓名,成绩,dbo.Getkemu(姓名) as 科目 from ChengJi2 group by 姓名, 成绩

网友一的方案：

select * into #tb from( select '张三' as name,60 as 语文,70 as 数学,80 as 英语 union select '李四' as name,90 as 语文,70 as 数学,90 as 英语 union select '王武' 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 #tb union all select name,数学 as 成绩,'数学' 科目 from #tb union all select 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); with tree as ( select [name], 分数, 科目 from #tmp unpivot ( 分数 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 t2 group by name, 分数 drop table #tmp

总结：

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

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

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

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