一个搞了很久的SQL

--drop table T_test
--drop table T_test2
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,b int,g int,h int
)
insert into T_test2
select 1,2,2,2 union all
select 1,2,2,2 union all
select 1,2,2,4 union all
select 1,2,2,4 union all
select 1,3,3,4 union all
select 1,3,3,1 union all
select 1,3,3,1

第一个表:
a,b,c,d,e,f
1 2 3 3 5 6
1 2 3 3 5 6
1 2 3 4 5 6
1 3 3 3 5 6
1 3 3 3 5 6
第二个表:
a,b,g,h
1 2 2 2
1 2 2 2
1 2 2 4
1 2 2 4
1 3 3 4
1 3 3 1
1 3 3 1
我要的结果:
我要的结果第一个表按a,b分组：
分别为：
a,b,(第一个表按a,b分组后c,d不同组合的count),sum(e),sum(f),(第二个表按a,b分组后，不同g,h组合的count)
即结果为:
1 2 2 15 18 2
1 3 1 10 12 2
我写的一个:
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 g,h from T_test2 where a=t.a and b=t.b)a)as coun2
from T_test t
别人帮我写的:
select tmpA.*, tmpB.[count] from
(
select a, b,
[count]=(select count(*) from (select distinct c,d from T_test where a=A.a and b=A.b group by c, d) tmpA),
e_sum=sum(e), f_sum=sum(f)
from T_test as A
group by a, b
)tmpA
left join
(
select a, b,
[count]=(select count(*) from (select distinct g, h from T_test2 where a=A.a and b=A.b group by g, h) tmpB)
from T_test2 as A
group by a, b
)tmpB on tmpA.a=tmpB.a and tmpA.b=tmpB.b
我自己又写的:
select a,b,
(select count(*) from (select distinct c,d from T_test where a=t.a and b=t.b group by c,d)a)as coun,
sum(e)as sum_e,sum(f) as sum_f,
(select count(*) from (select distinct g,h from T_test2 where a=t.a and b=t.b group by g,h)a)as coun2
from T_test T
group by a,b