一道归并成就最高科目的办理办法[MSSQL防范]

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

　　name 语文 数学 英语

　　张三 75 90 85

　　李四 80 85 85

　　得到表,查询每个 name 成就最高的学科, 若有相同,并列

　　name 成就 科目

　　张三 90 数学

　　李四 85 数学,英语

　　我的筹划:

　　[sql]

　　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 姓名, 成就 -www.2cto.com-

　　网友一的筹划:

　　[sql]

　　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

　　网友二的筹划:

　　[sql]

　　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 掌握的不纯熟.

　　网友二是我的筹划的改良版

　　摘自 德仔的专栏