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

原创 2012年03月28日 08:22:13

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

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

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




版权声明:本文为博主原创文章,未经博主允许不得转载。

高科电话机音量调节

    高科这类电话机默认音量忒大,而且一断电就要重设音量,鉴于很多同事还不知道怎么调节音量,所以这里说一下。首先看到话机右侧有个“设置”按钮吧,按一下(其他啥都不要动,也不要拿话筒),然后按2,再按...
  • magicbreaker
  • magicbreaker
  • 2008年02月15日 16:08
  • 25300

【bzoj2326】[HNOI2011]数学作业

矩阵乘法
  • KikiDMW
  • KikiDMW
  • 2017年03月31日 09:35
  • 215

中软高科Java培训专家:10个Java面试小技巧!

很多Java程序员要面临着各种面试问题,在面试的时候会紧张,会有种种的困难,“中软高科”Java培训班专家认为你要在进行Java面试的时候明白一些小技巧,会使你在HR面前加分不少哦,会帮助你在面试中化...
  • zrgk2208
  • zrgk2208
  • 2017年09月21日 17:05
  • 217

MYSQL 合并查询

问题:四个数据库表属于同一级别,拥有部分相同字段如id, pondid,fixcosttype,value,systime要求:四张表合并查询按照时间由早到晚倒序排列解决方案:Sql查询采用union...
  • whxnuaa
  • whxnuaa
  • 2016年12月27日 17:31
  • 131

无线通信中的最大比合并(MRC)分析

最大比合并是分集合并技术中的最优选择,相对于选择合并和等增益合并可以获得最好的性能,性能提升是由Array Gain带来的更高的信噪比,进而带来更好的误码率特性决定的。最大比合并(Maximal Ra...
  • feifei884431
  • feifei884431
  • 2016年05月17日 20:10
  • 4917

任意10个同学的成绩 求平均分,最高分和最低分

/**********************************************************  任意10个同学的成绩 求平均分,最高分和最低分  . QQ139767****...
  • zhaomu
  • zhaomu
  • 2006年05月30日 23:47
  • 2407

JSP内置对象:在同一个页面实现成绩输入及总成绩显示

在这个试验中,在同一个页面实现了成绩的输入和累加运算。 其中,input的value值发生了改变。分不清value和name的区别, 特意百度了一下:**value 属性为 input 元素设定值。...
  • qq_37387363
  • qq_37387363
  • 2017年07月11日 16:11
  • 134

汉邦高科监控视频删除后恢复

汉邦高科作为众多监控品牌中的一种,使用也是比较广泛的,但是监控视频经常会因为各种各样的原因导致其丢失损坏,那么遇到这种情况我们该怎么办呢?甲驭科技数据恢复中心小编教您如何对丢失的汉邦监控视频数据恢复。...
  • qq_33983359
  • qq_33983359
  • 2016年04月20日 16:50
  • 1752

【Bzoj2326】数学作业

题目描述小 C 数学成绩优异,于是老师给小 C 留了一道非常难的数学作业题:给定正整数 N 和 M,要求计算 Concatenate (1 .. N) Mod M 的值,其中 Concatenate ...
  • Zhayan9QvQ
  • Zhayan9QvQ
  • 2017年02月26日 15:59
  • 190

mssql 一对多的关系合并成一对一(用分割符隔开)

use Tempdb go --> -->    if not object_id(N'Tempdb..#A') is null drop table #A Go Create tabl...
  • xiongxyt2
  • xiongxyt2
  • 2014年07月22日 14:33
  • 888
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[MSSQL]一道合并成绩最高科目的解决方法
举报原因:
原因补充:

(最多只允许输入30个字)