select top 1 name, scoreT from (select name,sum(score) as scoreT from student group by name order by scoreT desc)
select top 1 name,sum(score) maxscore from student group by name order by sum(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)
照着2#的写了一下,结果每个人的都打印出来了,我只想打印sum()最大的那个,该怎么处理呢。
select name,max(sum(score)) //这里我描述错了,这样该怎么写 from student group by name having max(sum(score))
select top 1 name,score from (select name,score=sum(score) from student group by name)t order by score desc
so sorry to htl258 ,i forgot the "top 1" .. thanks a lot to you all.
---------------------------------------------------------------------------------- -- 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 name,max(score) from student group by name意思是按名字分组后,取每组内最大的score ,然后打印name max(score) 这该怎么写呢。
不能用 name 分组吧!name 都不一样,用 name 分组的话,查询出来的还不就是全部记录...'示例:取分组后组内某字段最大值对应的记录' use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( id char(3), no char(3) ) go --插入测试数据 insert into tb select '1','35' union all select '1','65' union all select '2','45' union all select '2','64' union all select '2','77' union all select '3','78' union all select '3','59' go --代码实现select [id],[no] from tb t where not exists(select 1 from tb where [id]=t.[id] and [no]>t.[no])/* id no ------------ 1 65 2 77 3 78 */
还是要这个:select name,score from student t where score=(select max(score) from student where name=t.name)
select top 1 name,sum(score) maxscore from student group by name order by sum(score) 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 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)换一下
name,
scoreT
from
(select name,sum(score) as scoreT
from student
group by name order by scoreT desc)
from student
group by name
order by sum(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 name,max(sum(score)) //这里我描述错了,这样该怎么写
from student
group by name
having max(sum(score))
from (select name,score=sum(score)
from student group by name)t
order by score desc
-- 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 行受影响)
*/
from student
group by name意思是按名字分组后,取每组内最大的score ,然后打印name max(score) 这该怎么写呢。
不能用 name 分组吧!name 都不一样,用 name 分组的话,查询出来的还不就是全部记录...'示例:取分组后组内某字段最大值对应的记录'
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id char(3),
no char(3)
)
go
--插入测试数据
insert into tb select '1','35'
union all select '1','65'
union all select '2','45'
union all select '2','64'
union all select '2','77'
union all select '3','78'
union all select '3','59'
go
--代码实现select [id],[no] from tb t
where not exists(select 1 from tb where [id]=t.[id] and [no]>t.[no])/*
id no
------------
1 65
2 77
3 78
*/
from student t
where score=(select max(score) from student where name=t.name)
from student
group by name
order by sum(score) desc
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
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)换一下
怎么感觉不是题目的意思呢...join语句什么作用, 我就学了数据库概论,SQL语句好多不懂.
老师布置这个作业难免太超前了