我做了四张视图
分别是
vw_item(商品表)vw_khdh(客户订货)
vw_dbdh(调拨订货)vw_scbh(市场配货)
我做的左联结代码是三个订货左联结商品表select a.spdm,khdh,scdh,dbdh from vw_item as a
left join (select spdm,sum(khdh) as khdh from vw_khdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as b on b.spdm=a.spdm
left join (select spdm,sum(scdh) as scdh from vw_scbh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as c on b.spdm=a.spdm
left join (select spdm,sum(dbdh) as dbdh from vw_dbdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as d on b.spdm=a.spdm where a.spdm='8096B29'
但是得出的数据不对.(后来我特地加了个a.spdm='8096B29') 结果8096b29跳出来1000多行重复项.商品表里面的货号都是唯一的.没有重复.而且如果我用其中的一订货左联结商品表是正确 的.但是再加个左联结就错了.不知道问题出在哪边>?希望高手帮忙解答.错误:只加一个左联结:
select a.spdm,khdh from vw_item as a
left join (select spdm,sum(khdh) as khdh from vw_khdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as b on b.spdm=a.spdm where a.spdm='8096B29'
图:
分别是
vw_item(商品表)vw_khdh(客户订货)
vw_dbdh(调拨订货)vw_scbh(市场配货)
我做的左联结代码是三个订货左联结商品表select a.spdm,khdh,scdh,dbdh from vw_item as a
left join (select spdm,sum(khdh) as khdh from vw_khdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as b on b.spdm=a.spdm
left join (select spdm,sum(scdh) as scdh from vw_scbh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as c on b.spdm=a.spdm
left join (select spdm,sum(dbdh) as dbdh from vw_dbdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as d on b.spdm=a.spdm where a.spdm='8096B29'
但是得出的数据不对.(后来我特地加了个a.spdm='8096B29') 结果8096b29跳出来1000多行重复项.商品表里面的货号都是唯一的.没有重复.而且如果我用其中的一订货左联结商品表是正确 的.但是再加个左联结就错了.不知道问题出在哪边>?希望高手帮忙解答.错误:只加一个左联结:
select a.spdm,khdh from vw_item as a
left join (select spdm,sum(khdh) as khdh from vw_khdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq<='2008-06-10 00:00:00.000' group by spdm)
as b on b.spdm=a.spdm where a.spdm='8096B29'
图:
left join (select spdm,sum(khdh) as khdh from vw_khdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq <='2008-06-10 00:00:00.000' group by spdm)
as b on b.spdm=a.spdm
left join (select spdm,sum(scdh) as scdh from vw_scbh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq <='2008-06-10 00:00:00.000' group by spdm)
as c on b.spdm=a.spdm
left join (select spdm,sum(dbdh) as dbdh from vw_dbdh where dm4='002'
and rq>='2008-03-01 00:00:00.000' and rq <='2008-06-10 00:00:00.000' group by spdm)
as d on b.spdm=a.spdm where a.spdm='8096B29'
连接条件错了
应该是
as c on c.spdm=a.spdm
as d on d.spdm=a.spdm