很郁闷的一个看似很难的SQL查询,各位给想想,谢放

[no_mIss]

具体业务太麻烦,我提供模拟业务表如下:
第一个表:
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 类的写法,谢放大家.

[no_mIss]

是谢谢大家,打错了.

[marco08]

select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b

[no_mIss]

to marco08(天道酬勤)
感谢,我不是想要这个结果

[marco08]

select tmpA.*, tmpB.[count] from
(
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
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 a,g) 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 3 15 18 1
1 3 2 10 12 2

(2 row(s) affected)

[子陌红尘]

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

[no_mIss]

太感谢了
libin_ftsafe的结果好象有点问题
marco08(天道酬勤)应该对了
我试试

[marco08]

--錯了, 改改
select tmpA.*, tmpB.[count] from
(
select a, b, [count]=count(*), e_sum=sum(e), f_sum=sum(f) from T_test
group by a, b
)tmpA
left join
(
select a, b, [count]=(select count(distinct g) from T_test2 where a=A.a and b=A.b)
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 3 15 18 2
1 3 2 10 12 1

(2 row(s) affected)

[子陌红尘]

maybe:

select
isnull(a.a,b.a) as a,
isnull(a.b,b.b) as b,
a.cnt1,
a.e,
a.f,
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
full outer join
(select a,b,count(*) as cnt2 from T_test2 group by a,b) b
on
a.a=b.a and a.b=b.b

[python二级题库]

xue xi!

[no_mIss]

libin_ftsafe 还是有问题,最后的那一列
marco08 的可以了
我仔细看看
谢谢你们