希望得到结果是:
name insum outsum
张三 100 80
李四 170 10 但实际结果是:
name insum outsum
张三 200 80
李四 170 0
declare @member table(mid int,name varchar(10))
insert into @member values(1,'张三')
insert into @member values(2,'李四')declare @income table(inid int,in_mid int,n1 int)
insert into @income values(1,1,100)
insert into @income values(2,2,80)
insert into @income values(3,2,90)declare @outcome table(oid int,o_inid int,n2 int)
insert into @outcome values(1,1,50)
insert into @outcome values(2,1,30)
insert into @outcome values(3,2,10)select m.name,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from @member m
left outer join
(select in_mid,sum(n1) n1 from @income group by in_mid) i
on m.mid=i.in_mid
left outer join
(select o_inid,sum(n2) n2 from @outcome group by o_inid) o
on m.mid=o.o_inid/*
name n1 n2
---------- ----------- -----------
张三 100 80
李四 170 10(所影响的行数为 2 行)
*/跟你希望得到的结果好像没什么区别
name insum outsum
张三 100 80
李四 170 10 但实际结果是:
name insum outsum
张三 200 80
李四 170 0
declare @member table(mid int,name varchar(10))
insert into @member values(1,'张三')
insert into @member values(2,'李四')declare @income table(inid int,in_mid int,n1 int)
insert into @income values(1,1,100)
insert into @income values(2,2,80)
insert into @income values(3,2,90)declare @outcome table(oid int,o_inid int,n2 int)
insert into @outcome values(1,1,50)
insert into @outcome values(2,1,30)
insert into @outcome values(3,2,10)select m.name,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from @member m
left outer join
(select in_mid,sum(n1) n1 from @income group by in_mid) i
on m.mid=i.in_mid
left outer join
(select o_inid,sum(n2) n2 from @outcome group by o_inid) o
on m.mid=o.o_inid/*
name n1 n2
---------- ----------- -----------
张三 100 80
李四 170 10(所影响的行数为 2 行)
*/跟你希望得到的结果好像没什么区别
mid name
1 张三
2 李四表二:【income】
inid in_mid n1
1 1 100
2 2 80
3 2 90表三:【outcome】
oid o_inid n2
1 1 30
2 1 50
3 2 10不知是否清楚。
谢谢。
create table member(mid int, name varchar(10))
insert member select 1, '张三'
insert member select 2, '李四'if object_id('income') is not null drop table income
create table income (inid int identity, in_mid int, n1 money)
insert income select 1,100
insert income select 2,80
insert income select 2,90if object_id('outcome') is not null drop table outcome
create table outcome (oid int identity, o_inid int, n2 money)
insert outcome select 1,50
insert outcome select 1,30
insert outcome select 2,10select m.name,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from member m
left outer join
(select in_mid,sum(n1) n1 from income group by in_mid) i
on m.mid=i.in_mid
left outer join
(select o_inid,sum(n2) n2 from outcome group by o_inid) o
on m.mid=o.o_inid-->确实没问题/*
name n1 n2
---------- --------------------- ---------------------
张三 100.00 80.00
李四 170.00 10.00
*/drop table member,income,outcome
name insum outsum
张三 100 80
李四 170 10 但实际结果是:
name insum outsum
张三 100 80
李四 170 0 我又测试了,但为什么和您的结果不一样?
我用的是MS SQLSERVER
这个不是问题吧。
用你给的数据重新测试
*/
--> 测试数据: member
if object_id('member') is not null drop table member
create table member (mid int,name varchar(4))
insert into member
select 1,'张三' union all
select 2,'李四'
--> 测试数据: income
if object_id('income') is not null drop table income
create table income (inid int,in_mid int,n1 int)
insert into income
select 1,1,100 union all
select 2,2,80 union all
select 3,2,90
--> 测试数据: outcome
if object_id('outcome') is not null drop table outcome
create table outcome (oid int,o_inid int,n2 int)
insert into outcome
select 1,1,30 union all
select 2,1,50 union all
select 3,2,10select m.name,isnull(i.n1,0) n1,isnull(o.n2,0) n2
from member m
left outer join
(select in_mid,sum(n1) n1 from income group by in_mid) i
on m.mid=i.in_mid
left outer join
(select o_inid,sum(n2) n2 from outcome group by o_inid) o
on m.mid=o.o_inid/*
name n1 n2
---- ----------- -----------
张三 100 80
李四 170 10
*/
我还以为是你用的MySQL呢.
a.name,sum(n1) n1,sum(n2) as n2
from
membe a
join
(select a.inid,a.in_mid,a.n1,sum(b.n2)as n2 from income a left join outcome b on a.inid=b.o_inid group by a.inid,a.in_mid,a.n1)B
on a.mid=b.in_mid
a.name,sum(n1) n1,sum(n2) as n2
from
membe a
left join--改为left join
(select a.inid,a.in_mid,a.n1,sum(b.n2)as n2 from income a left join outcome b on a.inid=b.o_inid group by a.inid,a.in_mid,a.n1)B
on a.mid=b.in_mid
a.name,sum(n1) n1,sum(n2) as n2
from
membe a
join
(select a.inid,a.in_mid,a.n1,sum(b.n2)as n2 from income a left join outcome b on a.inid=b.o_inid group by a.inid,a.in_mid,a.n1)B
on a.mid=b.in_mid
group by a.name
insert member select 1, '张三'
insert member select 2, '李四'
create table income (inid int identity, in_mid int, n1 money)
insert income select 1,100
insert income select 2,80
insert income select 2,90
create table outcome (oid int identity, o_inid int, n2 money)
insert outcome select 1,50
insert outcome select 1,30
insert outcome select 2,10select t.name ,
insum = (select sum(n1) from income where in_mid = t.mid),
outsum = (select sum(n2) from outcome where o_inid = t.mid)
from member t
/*
name insum outsum
---------- --------------------- ---------------------
张三 100.0000 80.0000
李四 170.0000 10.0000(所影响的行数为 2 行)
*/--考虑可能不匹配的情况。
select t.name ,
insum = isnull((select sum(n1) from income where in_mid = t.mid),0),
outsum = isnull((select sum(n2) from outcome where o_inid = t.mid),0)
from member t
/*
name insum outsum
---------- --------------------- ---------------------
张三 100.0000 80.0000
李四 170.0000 10.0000(所影响的行数为 2 行)
*/drop table member,income,outcome
a.name,sum(n1) n1,sum(n2) as n2
from
membe a
join
(select a.inid,a.in_mid,a.n1,sum(b.n2)as n2 from income a left join outcome b on a.inid=b.o_inid group by a.inid,a.in_mid,a.n1)B
on a.mid=b.in_mid
group by a.name这个对了,但为什么?
同样的数据,
为什么刚才的方案我和您的结果就不一样,而这次就一样了呢?
create table member(mid int, name varchar(10))
insert member select 1, '张三'
insert member select 2, '李四'if object_id('income') is not null drop table income
create table income (inid int identity, in_mid int, n1 money)
insert income select 1,100
insert income select 2,80
insert income select 2,90if object_id('outcome') is not null drop table outcome
create table outcome (oid int identity, o_inid int, n2 money)
insert outcome select 1,50
insert outcome select 1,30
insert outcome select 2,10
select
a.name,sum(n1) n1,sum(n2) as n2
from
member a
join
(select a.inid,a.in_mid,a.n1,sum(b.n2)as n2 from income a left join outcome b on a.inid=b.o_inid group by a.inid,a.in_mid,a.n1)B
on a.mid=b.in_mid
group by a.name/*
name n1 n2
---------- --------------------- ---------------------
李四 170.0000 10.0000
张三 100.0000 80.0000*/
insum = isnull((select sum(n1) from income where in_mid = t.mid),0),
outsum = isnull((select sum(n2) from outcome where o_inid = t.mid),0)
from member t
/*回复:代码20080512007 总:00000000007 */
/*主题:三表汇总 */
/*作者:二等草 */
/******************************************//************例子数据 begin****************/
declare @member table(mid int,name varchar(10))
insert into @member values(1,'张三')
insert into @member values(2,'李四')declare @income table(inid int,in_mid int,n1 int)
insert into @income values(1,1,100)
insert into @income values(2,2,80)
insert into @income values(3,2,90)declare @outcome table(oid int,o_inid int,n2 int)
insert into @outcome values(1,1,50)
insert into @outcome values(2,1,30)
insert into @outcome values(3,2,10)
/************例子数据 end******************//************代码 begin***************/
select a.mid,a.name,sum(n1),sum(n2) from
@member a
,(select in_mid,n1,(select sum(n2) from @outcome where o_inid = a.inid) n2 from @income a) b
where a.mid = b.in_mid
group by a.mid,a.name
order by a.mid/************代码 end*****************//************结果 begin***************
mid name
----------- ---------- ----------- -----------
1 张三 100 80
2 李四 170 10
************结果 end*****************//************清除*************************/