有表t1 表t2 表t3
L1 L2 L3 L1 L2 L1 L2
a1 b1 c1 b1 3 c1 5
a2 b2 c2 b1 8 c1 7
b2 5 c2 7
b2 4 c2 9请问高手怎样用SQL语句查询得到:L1 L2 L3
a1 11 12
b2 9 16
L1 L2 L3 L1 L2 L1 L2
a1 b1 c1 b1 3 c1 5
a2 b2 c2 b1 8 c1 7
b2 5 c2 7
b2 4 c2 9请问高手怎样用SQL语句查询得到:L1 L2 L3
a1 11 12
b2 9 16
a1 11 12
a2 9 16
--> 测试数据: [t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1] (L1 varchar(2),L2 varchar(2),L3 varchar(2))
insert into [t1]
select 'a1','b1','c1' union all
select 'a2','b2','c2'
--> 测试数据: [t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2] (L1 varchar(2),L2 int)
insert into [t2]
select 'b1',3 union all
select 'b1',8 union all
select 'b2',5 union all
select 'b2',4
--> 测试数据: [t3]
if object_id('[t3]') is not null drop table [t3]
create table [t3] (L1 varchar(2),L2 int)
insert into [t3]
select 'c1',5 union all
select 'c1',7 union all
select 'c2',7 union all
select 'c2',9select a.l1,b.l2,c.l3 from t1 a,
(select l1,l2=sum(l2) from t2 group by l1)b,
(select l1,l3=sum(l2) from t3 group by l1)c
where a.l2=b.l1 and a.l3=c.l1
declare @t1 table (l1 varchar(10),l2 varchar(10),l3 varchar(10))
insert into @t1 select 'a1','b1','c1'
union all select 'a2','b2','c2'
declare @t2 table (l1 varchar(10),l2 int)
insert into @t2 select 'b1',3
union all select 'b1',8
union all select 'b2',5
union all select 'b2',4
declare @t3 table (l1 varchar(10),l2 int)
insert into @t3 select 'c1',5
union all select 'c1',7
union all select 'c2',7
union all select 'c2',9select a.l1,sum(b.l2) l2 ,sum(c.l3) l3 from
@t1 a join (select l1,SUM(l2) l2 from @t2 group by l1) as b on a.l2=b.l1 join
(select l1,sum(l2) l3 from @t3 group by l1) c
on a.l3=c.l1 group by a.l1
l1 l2 l3
---------- ----------- -----------
a1 11 12
a2 9 16(2 行受影响)
L2 = (select sum(t2.L2) from t2 where t2.L1 = t1.L2) ,
L3 = (select sum(t3.L2) from t3 where t3.L1 = t1.L3)
from t1/*
L1 L2 L3
---- ----------- -----------
a1 11 12
a2 9 16(所影响的行数为 2 行)*/