查询语句问题

[bbbb04]

一张主表对应两张子表
主表字段：ID（单号），SL（数量）
子表1：ID（单号），AMC（名称）,NDB（含量），ZL（重量）
子表2：ID（单号），BMC（名称）,BFB（含量），ZL（重量）

现需要实现的查询结果是主表各单号所对应的子表1中NDB、及ZL的合计；子表2中ZL的合计。
下面语句实现的结果合计值都是错的
select a.ID,a.sl,sum(b.ndb),sum(b.zl),sum(c.zl) from a,b,c
where b.ID = a.ID and c.ID = a.ID
group by a.ID,a.sl


如下主表分别与子表关联查出的结果是对的，为什么上面的语句写到一起就出错，是哪写错了吗？
select a.ID,a.sl,sum(b.ndb2),sum(b.zl) from a
where b.ID = a.ID
group by a.ID,a.sl

select a.ID,a.sl,sum(c.zl) from rsjh a
where c.ID = a.ID
group by a.ID,a.sl

[PB菜鸟]

select a.ID,a.sl,sum(b.ndb2),sum(b.zl),0 c from a 
where b.ID = a.ID 
group by a.ID,a.sl
union all
select a.ID,a.sl,0 a ,0 b,sum(c.zl)  from rsjh a 
where c.ID = a.ID 
group by a.ID,a.sl

[PB菜鸟]

select a.ID,a.sl,sum(b.ndb2),sum(b.zl),0 c from a ,b where b.ID = a.ID group by a.ID,a.sl union all select a.ID,a.sl,0 a ,0 b,sum(c.zl) from rsjh a ,c where c.ID = a.ID group by a.ID,a.sl

[bbbb04]

引用 3 楼 smilysoft 的回复:

select a.ID,a.sl,sum(b.ndb2),sum(b.zl),0 c from a ,b where b.ID = a.ID group by a.ID,a.sl union all select a.ID,a.sl,0 a ,0 b,sum(c.zl) from rsjh a ,c where c.ID = a.ID group by a.ID,a.sl

这个试过，同一个ID查询结果出现两条，与想得到的结果不同（一个ID只出现一条查询结果）。

[dyf0130]

主表的Key是 ID（单号） 从表Key是ID（单号）+啥？ select a.ID,a.sl,sum(b.ndb2),sum(b.zl) from a --少写了点啥把 a,b where b.ID = a.ID group by a.ID,a.sl

[PB菜鸟]

引用 4 楼 bbbb04 的回复:

引用 3 楼 smilysoft 的回复:select a.ID,a.sl,sum(b.ndb2),sum(b.zl),0 c from a ,b where b.ID = a.ID group by a.ID,a.sl union all select a.ID,a.sl,0 a ,0 b,sum(c.zl) from rsjh a ,c where c.……

查询处理后再再group by 就是唯一的了。

[bbbb04]

引用 5 楼 smilysoft 的回复:

引用 4 楼 bbbb04 的回复: 引用 3 楼 smilysoft 的回复:select a.ID,a.sl,sum(b.ndb2),sum(b.zl),0 c from a ,b where b.ID = a.ID group by a.ID,a.sl 查询处理后再再group by 就是唯一的了。 ……

不明白，语句应该如何写？

[WorldMobile]

写一块当然结果不对，试试以下写法 select a.ID,a.sl,sum(b.ndb2),sum(b.zl), (select sum(c.zl) from c where a.id = c.id) from a, b where b.ID = a.ID group by a.ID,a.sl

[WorldMobile]

select a.ID,a.sl,sum(b.ndb2),sum(b.zl), (select sum(c.zl) from c where a.id = c.id) from a, b where a.ID = b.id (+) group by a.ID,a.sl

[bbbb04]

引用 8 楼 lzp_lrp 的回复:

select a.ID,a.sl,sum(b.ndb2),sum(b.zl), (select sum(c.zl) from c where a.id = c.id) from a, b where a.ID = b.id (+) group by a.ID,a.sl

试了下还可以，又出现新问题了，主表所对应的子表，可能会出现子表中不一定同时有数据的情况，这样上面执行的结果就会丢失数据。 (+) ? 这个是起什么作用的？

[WorldMobile]

试了下还可以，又出现新问题了，主表所对应的子表，可能会出现子表中不一定同时有数据的情况，这样上面执行的结果就会丢失数据。 (+)就是做这个用的 select a.ID,a.sl,sum(b.ndb2),sum(b.zl), (select sum(c.zl) from c where a.id = c.id) from a, b where a.ID = b.id (+) group by a.ID,a.sl 或者 select a.ID,a.sl,sum(b.ndb2),sum(b.zl), (select sum(c.zl) from c where a.id = c.id) from a, b where a.ID (+)= b.id group by a.ID,a.sl

[家中蛋]

// 以下语法基于 SQL SERVER 2000 select a.ID,a.sl, sumOfbndb = (select sum(isnull(b.ndb,0)) from b where b.ID = a.ID), sumOfbzl = (select sum(isnull(b.zl,0)) from b where b.ID = a.ID), sumOfczl = (select sum(isnull(c.zl,0)) from z where z.ID = a.ID) from a ;