表a:userid,username
表b:userid,incost,
表c:userid,outcost
现在要查出这样一个结果集:userid ,username,sum(incost),sum(outcost)
我的sql如下:
select a.userid,(a.username),sum(b.incost) as sumincost,sum(c.outcost) as sumoutcost
from xxzx_user_zyl a,xxzx_incost b, xxzx_outcost c
where a.userid=b.userid and b.userid=c.userid
group by a.userid,a.username结果查出来的结果总是不对,求高人指导
表b:userid,incost,
表c:userid,outcost
现在要查出这样一个结果集:userid ,username,sum(incost),sum(outcost)
我的sql如下:
select a.userid,(a.username),sum(b.incost) as sumincost,sum(c.outcost) as sumoutcost
from xxzx_user_zyl a,xxzx_incost b, xxzx_outcost c
where a.userid=b.userid and b.userid=c.userid
group by a.userid,a.username结果查出来的结果总是不对,求高人指导
这个应该用full join 吧
select a.userid,
a.username,
sum(b.incost) sumincost,
sum(c.outcost) sumoutcost
from xxzx_user_zyl a,xxzx_incost b, xxzx_outcost c
where a.userid=b.userid
and b.userid=c.userid
group by a.userid,a.username
应该没什么问题呀 可以列举数据看下么?
xxzx_user_zyl a,xxzx_incost b, xxzx_outcost cWHERE a.userid=b.userid(+)
AND a.userid=c.userid(+)
是以A表为准,进行外关联
原来的sql会造成一个很严重的后果,
我把sql改了一下,
select a.userid,a.username,aa.sumincost,bb.sumoutcost,aa.sumincost-bb.sumoutcost as yue
from xxzx_user_zyl a,
(select t.userid ,sum(t.incost) as sumincost from xxzx_incost t group by t.userid) aa,
(select b.userid,sum(b.outcost) as sumoutcost from xxzx_outcost b group by b.userid) bb
where aa.userid=a.userid and aa.userid=bb.userid 这样就行了