34,588
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-09 12:49:09
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [student]
IF OBJECT_ID('[student]') IS NOT NULL
DROP TABLE [student]
GO
CREATE TABLE [student] ([name] [nvarchar](10),[score] [int])
INSERT INTO [student]
SELECT 'a','20' UNION ALL
SELECT 'b','40' UNION ALL
SELECT 'a','40' UNION ALL
SELECT 'b','60' UNION ALL
SELECT 'c','59' UNION ALL
SELECT 'c','64' UNION ALL
SELECT 'd','89' UNION ALL
SELECT 'd','93' UNION ALL
SELECT 'e','98' UNION ALL
SELECT 'e','99' UNION ALL
SELECT 'f','100' UNION ALL
SELECT 'f','80'
--SELECT * FROM [student]
-->SQL查询如下:
select top 1 name,sum(score) maxscore
from student
group by name
order by sum(score) desc
/*
name maxscore
---------- -----------
e 197
(1 行受影响)
*/
select top 1 name,score
from (select name,score=sum(score)
from student group by name)t
order by score desc
WITH T AS
(select name,sum(score) AS total
from student
group by NAME)
SELECT * FROM T b
WHERE NOT EXISTS (SELECT 1 FROM T WHERE total>b.total)
select top 1 name,sum(score) maxscore
from student
group by name
order by sum(score) desc
select top 1
name,
scoreT
from
(select name,sum(score) as scoreT
from student
group by name order by scoreT desc)
select a.项目编号, b.项目名称,c.运动员姓名,sum(a.积分) 总积分
from 成绩 a
join 项目 b on a.项目编号=b.项目编号
join 运动会 c on a.运动员编号=c.运动员编号
group by a.项目编号,b.项目名称,c.运动员姓名
having sum(a.积分)=(select top 1 sum(积分) from 成绩 where 项目编号=a.项目编号 order by sum(积分) desc)
换一下select b.项目名称,c.运动员姓名
from 成绩 a
join 项目 b on a.项目编号=b.项目编号
join 运动会 c on a.运动员编号=c.运动员编号
group by b.项目名称,c.运动员姓名
having sum(a.积分)=(select top 1 sum(积分) from 成绩 where 项目编号=a.项目编号 order by sum(积分) desc)
try还是要这个:
select name,score
from student t
where score=(select max(score) from student where name=t.name)