问一个稍微复杂点的SQL语句该怎么写,谢谢。在线等。有三张表A,B,C
A表中有a1,a2,a3三列
B表中有a2,b1,b2三列(B表中的a2列和A表中的a2列一一对应)
C表中有b1,c1,c3三列(C表中的b1列和B表中的b1列一一对应)
例子数据
A表:
a1 a2 a3
-------------------
ttt hhh vvv
sss uuu wwwB表:
a2 b1 b2
-------------------
hhh iii mmm
uuu jjj nnn
C表:
b1 c1 c2
-------------------
iii 111 112
iii 222 113
jjj 333 223
jjj 444 224现在我要实现以下功能:
根据b1列统计C表的c1,c2列的和,并从B表和A表中取出a2列的对应数据,我想要的结果如下:
b1 a1 a2 sum(c1) sum(c2)
----------------------------------
iii ttt hhh 333 225
jjj sss uuu 777 447
A表中有a1,a2,a3三列
B表中有a2,b1,b2三列(B表中的a2列和A表中的a2列一一对应)
C表中有b1,c1,c3三列(C表中的b1列和B表中的b1列一一对应)
例子数据
A表:
a1 a2 a3
-------------------
ttt hhh vvv
sss uuu wwwB表:
a2 b1 b2
-------------------
hhh iii mmm
uuu jjj nnn
C表:
b1 c1 c2
-------------------
iii 111 112
iii 222 113
jjj 333 223
jjj 444 224现在我要实现以下功能:
根据b1列统计C表的c1,c2列的和,并从B表和A表中取出a2列的对应数据,我想要的结果如下:
b1 a1 a2 sum(c1) sum(c2)
----------------------------------
iii ttt hhh 333 225
jjj sss uuu 777 447
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(a1 varchar(8), a2 varchar(8), a3 varchar(8))
insert into #a
select 'ttt', 'hhh', 'vvv' union all
select 'sss', 'uuu', 'www'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(a2 varchar(8), b1 varchar(8), b2 varchar(8))
insert into #b
select 'hhh', 'iii', 'mmm' union all
select 'uuu', 'jjj', 'nnn'
--> 测试数据:#c
if object_id('tempdb.dbo.#c') is not null drop table #c
create table #c(b1 varchar(8), c1 int, c2 int)
insert into #c
select 'iii', 111, 112 union all
select 'iii', 222, 113 union all
select 'jjj', 333, 223 union all
select 'jjj', 444, 224select
c.b1, max(a.a1)a1, max(a.a2)a2, sum(c.c1)c1, sum(c.c2)c2
from
#a a, #b b, #c c where a.a2=b.a2 and b.b1=c.b1
group by c.b1/*
b1 a1 a2 c1 c2
-------- -------- -------- ----------- -----------
iii ttt hhh 333 225
jjj sss uuu 777 447
*/
select t2.b1,t1.a1,t1.a2,t3.c1,t3.c2
from [A表] t1,[B表] t2,
(select b1,sum(c1),sum(c2) from [C表] group by b1) t3
where t1.a2=t2.a2 and t2.b1=t3.b1
select distinct c.b1, a.a1, a.a2, c.c1, c.c2 from #a a, #b b,
(select b1, sum(c1)c1, sum(c2)c2 from #c group by b1) c
where a.a2=b.a2 and b.b1=c.b1/*
b1 a1 a2 c1 c2
-------- -------- -------- ----------- -----------
iii ttt hhh 333 225
jjj sss uuu 777 447
*/