select tmpA.*, tmpB.[count] from
(
select a, b, [count]=(select top 1 count(*) from T_test where a=A.a and b=A.b group by c, d),
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 top 1 count(*) from T_test2 where a=A.a and b=A.b group by g, h)
from T_test2 as A
group by
a, b
)tmpB on tmpA.a=tmpB.a and tmpA.b=tmpB.b
(
select a, b, [count]=(select top 1 count(*) from T_test where a=A.a and b=A.b group by c, d),
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 top 1 count(*) from T_test2 where a=A.a and b=A.b group by g, h)
from T_test2 as A
group by
a, b
)tmpB on tmpA.a=tmpB.a and tmpA.b=tmpB.b
第一个表按a,b分组后c,d不同组合的count
这个列不对,
a=1,b=3时,c,d的不同组合应该是1,你的是2
select a,b,
(select count(*) from (select distinct c,d from T_test where a=t.a and b=t.b) tmpa) as cou
from T_test t
group by a,b
而这个却行(结果正确)?
select a,b,
(select count(distinct c) from T_test where a=t.a and b=t.b) as cou
from T_test t
group by a,b
(
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 --result
a b count e_sum f_sum count
----------- ----------- ----------- ----------- ----------- -----------
1 2 2 15 18 2
1 3 1 10 12 2(2 row(s) affected)
谢谢,结果是对了..但不知道为什么:
select distinct c,d from T_test where a=A.a and b=A.b group by c, d
这里还要group by c,d呢?
(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