要求 汇总按时间排序的每个人的最后两次的成绩的和,怎么写SQL? 表和数据如下:
ID 时间 姓名 分数1 2007-12-25 张三 80
2 2007-12-26 张三 90
3 2007-12-27 张三 98
4 2007-12-28 张三 85
5 2007-12-29 张三 70
6 2007-12-25 李四 80
7 2007-12-26 李四 90
8 2007-12-27 李四 98
9 2007-12-28 李四 85
10 2007-12-29 李四 70
11 2007-12-25 王五 80
12 2007-12-26 王五 90
13 2007-12-27 王五 98
14 2007-12-28 王五 85
15 2007-12-29 王五 70
ID 时间 姓名 分数1 2007-12-25 张三 80
2 2007-12-26 张三 90
3 2007-12-27 张三 98
4 2007-12-28 张三 85
5 2007-12-29 张三 70
6 2007-12-25 李四 80
7 2007-12-26 李四 90
8 2007-12-27 李四 98
9 2007-12-28 李四 85
10 2007-12-29 李四 70
11 2007-12-25 王五 80
12 2007-12-26 王五 90
13 2007-12-27 王五 98
14 2007-12-28 王五 85
15 2007-12-29 王五 70
union all select 2,'2007-12-26','张三',90
union all select 3,'2007-12-27','张三',98
union all select 4,'2007-12-28','张三',85
union all select 5,'2007-12-29','张三',70
union all select 6,'2007-12-25','李四',80
union all select 7,'2007-12-26','李四',90
union all select 8,'2007-12-27','李四',98
union all select 9,'2007-12-28','李四',85
union all select 10,'2007-12-29','李四',70
union all select 11,'2007-12-25','王五',80
union all select 12,'2007-12-26','王五',90
union all select 13,'2007-12-27','王五',98
union all select 14,'2007-12-28','王五',85
union all select 15,'2007-12-29','王五',70select 姓名,sum(分数) from @a a where id in(select top 2 id from @a where 姓名=a.姓名 order by 时间 desc) group by 姓名
--result
/*姓名
-------------------- -----------
李四 155
王五 155
张三 155(所影响的行数为 3 行)*/
set nocount on
create table test(ID varchar(20),时间 varchar(20),姓名 varchar(20),分数 int)
insert into test select '1','2007-12-25','张三','80'
insert into test select '2','2007-12-26','张三','90'
insert into test select '3','2007-12-27','张三','98'
insert into test select '4','2007-12-28','张三','85'
insert into test select '5','2007-12-29','张三','70'
insert into test select '6','2007-12-25','李四','80'
insert into test select '7','2007-12-26','李四','90'
insert into test select '8','2007-12-27','李四','98'
insert into test select '9','2007-12-28','李四','85'
insert into test select '10','2007-12-29','李四','70'
insert into test select '11','2007-12-25','王五','80'
insert into test select '12','2007-12-26','王五','90'
insert into test select '13','2007-12-27','王五','98'
insert into test select '14','2007-12-28','王五','85'
insert into test select '15','2007-12-29','王五','70'
go
--测试
select 姓名,sum(分数) as 总分
from test a
where exists(select 姓名 from test
where 姓名=a.姓名 and 时间>=a.时间 group by 姓名
having(count(*))<=2)
group by 姓名--删除测试环境
drop table test
set nocount off/*
李四 155
王五 155
张三 155
*/
select 姓名,sum(分数) as 最后两次的成绩的和
from 表 a
where id in (select top 2 id from 表 where 姓名 =a.姓名 order by 时间 desc)
group by 姓名--ps:如果某个姓名只有一条记录,那 最后两次的成绩的和 只是一次成绩
insert into @a select 1,'2007-12-25','张三',1
insert into @a select 2,'2007-12-26','张三',2
insert into @a select 3,'2007-12-27','张三',3
insert into @a select 4,'2007-12-25','李四',1
insert into @a select 5,'2007-12-26','李四',2
insert into @a select 6,'2007-12-27','李四',3
select name ,sum(num) as'总和' from @a a where id in(
select top 2 id from @a
where name=a.name order by dt desc)
group by namename 总和
李四 5
张三 5