刚才那个SQL查询有点不对呀....超难.....

[no_mIss]

原贴:http://community.csdn.net/Expert/TopicView.asp?id=5299080
当然没看清,发的有问题,答的也不对其实,再重新发下,希望能表明我的意思.

--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
感觉效率不高,所以想再求个group by 的写法,再请帮忙!

[marco08]

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

[no_mIss]

to:marco08
第一个表按a,b分组后c,d不同组合的count
这个列不对,
a=1,b=3时,c,d的不同组合应该是1,你的是2

[marco08]

錯了, 正在試試

[no_mIss]

嗯.谢谢,还有个问题一直搞不懂

为什么像下面这种查询不行(结果不对)？
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

[marco08]

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

--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)

[marco08]

不知對不對, 調了好久

[no_mIss]

to:marco08
谢谢,结果是对了..

但不知道为什么:
select distinct c,d from T_test where a=A.a and b=A.b group by c, d
这里还要group by c,d呢?

[no_mIss]

爱到你的启发,这样写就可以了:

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