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

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




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

相关文章推荐

MSSQL数据库被插入JS/sql注入挂马的解决方法

MSSQL数据库被插入JS/sql注入挂马的解决方法黑客先从搜索引擎google、百度等搜索存在漏洞的采用asp+mssql设计的网站,然后采用小明子这样的注入扫描工具,扫描整个网站,一旦发现有sql...

mssql2005 远程连接问题解决方法

一、为 SQL 启用远程连接 1. 单击“开始”,依次指向“程序”、“Microsoft SQL Server 2005”和“配置工具”,然后单击“SQL Server 外围应用配置器”。 2...

php mssql2000 text字段 被截断读取不完整的问题解决方法

php mssql2000 text字段 被截断读取不完整的问题解决方法   在抓取页面的时候保存源文件是很好的习惯,如果有需要可以照旧重新抓取,页面文件平均1W字节左右,于是使用了text字段 问题...

多线程频繁操作MSSQL导致java.net.BindException异常的解决方法

现象:[1]多线程启动频繁操作MSSQL,抛出到主机  的 TCP/IP 连接失败。 java.net.BindException: Address already in use: connect [...

mssql孤立用户的解决方法

'当您将数据库备份恢复到另一台服务器时,可能会遇到孤立用户的问题。以下情形说明了该问题并阐述如何加以解决。 1. 向主数据库添加一个登录,并将默认数据库指定为 Northwind: Use maste...

MSSQL SERVER中出现服务器MSDTC不可用的解决方法

1. A.不用事务,关用SELECT   语句.是否可以分布式查询?     B.LINKSERVER   在做分布式更新事务时不能对本机操作.(就是不能环回分布式事务)  C.DBCC &...

sql server 取每一个学生多个科目当中分数最高的记录……

同表当中,取每一个学生多个科目当中分数最高的记录(name,sub,scores)WITH tmp AS(SELECT stuid,sub,scores,ROW_NUMBER() OVER (PART...

黑马程序员-一一道面试题的解决方法

---------------------- android培训、java培训、期待与您交流! ---------------------- 有时间看了看张孝祥老师的面试宝典,真是泄气,好多题都...

MSSql 自定义函数实现小写金额转换成大写(最高13位不包括小数点后两位)

今天做项目的时候,需要生成WORD文档,然后要求把小写金额全部转换为大写,于是乎分享代码---功能:小写金额转换成大写(最高13位不包括小数点后两位)
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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