分组查询出结果集，然后再根据结果集分组查询统计

[zbz2188]

我想分组查询出一个结果集，然后再根据结果集分组查询统计
比如有一个表A.字段有id,score,mark,record,dep,它们都是int型
我想以dep分组，每组里，要record最小。最后以record分组统计
数据如下
id score mark record dep
1 1.0 1.0 1 1
2 2.0 2.0 1 2
3 3.0 3.0 1 3
4 2.0 2.0 2 2
5 2.0 2.0 3 2
6 4.0 4.0 2 4
请各位大虾指点,谢谢了

[dawugui]

select record , sum(score) score , sum(mark) mark from
(
select a.* from tb a,
(select dep , min(record) record from tb group by dep) b
where a.dep = b.dep and a.record = b.record
) t
group by record

[zbz2188]

第一次查询结果是这样的
id score mark record dep
1 1.0 1.0 1 1
2 3.0 2.0 1 2
3 3.0 3.0 1 3
6 4.0 4.0 2 4

[zbz2188]

第二次结果是
score mark record
7.0 6.0 1
4.0 4.0 2

[dawugui]

create table tb(id int,score int,mark int,record int,dep int)
insert into tb values(1, 1.0, 1.0, 1, 1)
insert into tb values(2, 2.0, 2.0, 1, 2)
insert into tb values(3, 3.0, 3.0, 1, 3)
insert into tb values(4, 2.0, 2.0, 2, 2)
insert into tb values(5, 2.0, 2.0, 3, 2)
insert into tb values(6, 4.0, 4.0, 2, 4)
go

--以dep分组，每组里，要record最小
select a.* from tb a,
(select dep , min(record) record from tb group by dep) b
where a.dep = b.dep and a.record = b.record
/*
id score mark record dep
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
2 2 2 1 2
3 3 3 1 3
6 4 4 2 4
*/

--最后以record分组统计
select record , sum(score) score , sum(mark) mark from
(
select a.* from tb a,
(select dep , min(record) record from tb group by dep) b
where a.dep = b.dep and a.record = b.record
) t
group by record
/*
record score mark
----------- ----------- -----------
1 6 6
2 4 4
*/
drop table tb

[zbz2188]

你以前也回复过我的一个贴子，谢谢你