很郁闷的一个看似很难的SQL查询,各位给想想,谢放
具体业务太麻烦,我提供模拟业务表如下:
第一个表:
create   table   T_test
(id   int   identity(1,1)   primary   key,
a   int,b   int   ,c   int   ,d   int,   e   int   ,f   int   )
insert   into   t_test
select   1,2,3,3,5,6   union   all
select   1,2,3,3,5,6   union   all
select   1,2,3,4,5,6   union   all
select   1,3,3,3,5,6   union   all
select   1,3,3,3,5,6  

第二个表:
create   table   T_test2
(
id   int   identity(1,1)   primary   key,
a   int,g   int,b   int
)
insert   into   T_test2
select   1,2,2   union   all
select   1,1,2   union   all
select   1,2,2   union   all
select   1,2,3   union   all
select   1,2,3  

要的结果用数据说吧:
第一个表：id,a,b,c,d,e,f
1 1 2 3 3 5 6
2 1 2 3 3 5 6
3 1 2 3 4 5 6
4 1 3 3 3 5 6
5 1 3 3 3 5 6
第二个表：id,a,g,b
1 1 2 2
2 1 1 2
3 1 2 2
4 1 2 3
5 1 2 3
 
我要的结果第一个表按a,b分组：
分别为：a,b,(第一个表按a,b分组后的count),sum(e),sum(f),(第二个表按a,b分组后，不同a,g组合的count)
1 2 2 15 18 2
1 3 1 10 12 1  

目前我写出了一种方法:
select   distinct   a,b,
(select   count(*)   from   (select   distinct   c,d   from   T_test   where   a=t.a   and   b=t.b)a)as   coun,
(select   sum(e)   from   T_test   where   a=t.a   and   b=t.b)   as   sum_e,
(select   sum(f)   from   T_test   where   a=t.a   and   b=t.b)   as   sum_f,
(select   count(*)   from   (select   distinct   a,g   from   T_test2   where   a=t.a   and   b=t.b)a)as   coun2
from   T_test   t

但效率不高,所以请教用group   by   类的写法,谢放大家.

------解决方案--------------------
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b
------解决方案--------------------
create table T_test
(id int identity(1,1) primary key,
a int,b int ,c int ,d int, e int ,f int )
insert into t_test
select 1,2,3,3,5,6 union all
select 1,2,3,3,5,6 union all
select 1,2,3,4,5,6 union all
select 1,3,3,3,5,6 union all
select 1,3,3,3,5,6

create table T_test2
(
id int identity(1,1) primary key,
a int,g int,b int
)
insert into T_test2
select 1,2,2 union all
select 1,1,2 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,3
go


select
a.*,b.cnt2
from
(select a,b,count(*) as cnt1,sum(e) as e,sum(f) as f from T_test group by a,b) a,
(select a,b,count(*) as cnt2 from T_test2 group by a,b) b
where
a.a=b.a and a.b=b.b
go


/*
a b cnt1 e f cnt2
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 15 18 3
1 3 2 10 12 2
*/

drop table t_test,t_test2
go
------解决方案--------------------
--錯了, 改改
select tmpA.*, tmpB.[count] from
(
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) fro