[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 掌握的不熟練。

網友二是我的方案的改進版