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

1962人阅读 评论(0) 收藏 举报

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

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

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




1
1

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2382425次
    • 积分:35035
    • 等级:
    • 排名:第141名
    • 原创:897篇
    • 转载:491篇
    • 译文:6篇
    • 评论:272条
    联系方式
    • ERP群:68148773
    • C#群: 254167627
    • SQL群:446802573
    • 会计群:426046886
    • 安卓群:604859607
    • ext群: 556270614
    • Tea群: 126748158
    • 德仔工作室
    • 德仔淘宝店
    鼎捷易飞精英群
    博客专栏
    最新评论
    文章存档