分组查询出结果集，然后再根据结果集分组查询统计
我想分组查询出一个结果集，然后再根据结果集分组查询统计
比如有一个表A.字段有id,score,mark,record,dep,它们都是int型
我想以dep分组，每组里，要record最小。最后以record分组统计
数据如下
id score mark record dep
1 1.0 1.011
2 2.0 2.012
3 3.0 3.013
4 2.0 2.022
5 2.0 2.032
6 4.0 4.024
请各位大虾指点,谢谢了
[解决办法]
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
[解决办法]
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
