三个表T_Xfxx,T_Xftc,T_Xfzs 对三个表操作对数量字段汇总并形成一个视图select dbo.T_Xfxx.ID,dbo.T_Xfxx.Spmc,dbo.T_Xfxx.Spdw,dbo.T_Xfxx.Spdj,dbo.T_Xfxx.Spsl,
SUM(ISNULL(dbo.T_Xftc.Tcsl,0)) as Sptc,SUM(ISNULL(dbo.T_Xfzs.Zssl,0)) as Spzs,
dbo.T_Xfxx.Spsl-SUM(ISNULL(dbo.T_Xftc.Tcsl,0))-SUM(ISNULL(dbo.T_Xfzs.Zssl,0)) as sl,
(dbo.T_Xfxx.Spsl-SUM(ISNULL(dbo.T_Xftc.Tcsl,0))-SUM(ISNULL(dbo.T_Xfzs.Zssl,0)))* dbo.T_Xfxx.Spdj as hj
from dbo.T_Xfxx left outer join dbo.T_Xftc on dbo.T_Xfxx.ID=dbo.T_Xftc.XfID left outer join dbo.T_Xfzs on dbo.T_Xfxx.ID=dbo.T_Xfzs.XfID
where dbo.T_Xfxx.Djbh='K201301071249'
group by dbo.T_Xfxx.ID,dbo.T_Xfxx.Spdw,dbo.T_Xfxx.Spdj,dbo.T_Xfxx.Spsl,dbo.T_Xfxx.Spmc得到结果如下:
ID Spmc spdw Spdj Spsl Sptc Spzs sl hj
1 商品1 份 100 1 0 0 1 100
2 商品2 份 20 10 2 0 8 160
3 商品3 份 8 10 0 2 8 64
4 商品4 份 8 5 2 2 1 8如果对T_Xfzs这个表单独查询select XfID,sum(Zssl) as Spzs from T_Xfzs where Djbh='K201301071249' group by XfID,Zssl会得到不同的结果如下:
XfID Spzs
3 2
4 1
对T_Xfzs表单独查询的结果是正确的,那么上面那条语句在哪出了问题?
SUM(ISNULL(dbo.T_Xftc.Tcsl,0)) as Sptc,SUM(ISNULL(dbo.T_Xfzs.Zssl,0)) as Spzs,
dbo.T_Xfxx.Spsl-SUM(ISNULL(dbo.T_Xftc.Tcsl,0))-SUM(ISNULL(dbo.T_Xfzs.Zssl,0)) as sl,
(dbo.T_Xfxx.Spsl-SUM(ISNULL(dbo.T_Xftc.Tcsl,0))-SUM(ISNULL(dbo.T_Xfzs.Zssl,0)))* dbo.T_Xfxx.Spdj as hj
from dbo.T_Xfxx left outer join dbo.T_Xftc on dbo.T_Xfxx.ID=dbo.T_Xftc.XfID left outer join dbo.T_Xfzs on dbo.T_Xfxx.ID=dbo.T_Xfzs.XfID
where dbo.T_Xfxx.Djbh='K201301071249'
group by dbo.T_Xfxx.ID,dbo.T_Xfxx.Spdw,dbo.T_Xfxx.Spdj,dbo.T_Xfxx.Spsl,dbo.T_Xfxx.Spmc得到结果如下:
ID Spmc spdw Spdj Spsl Sptc Spzs sl hj
1 商品1 份 100 1 0 0 1 100
2 商品2 份 20 10 2 0 8 160
3 商品3 份 8 10 0 2 8 64
4 商品4 份 8 5 2 2 1 8如果对T_Xfzs这个表单独查询select XfID,sum(Zssl) as Spzs from T_Xfzs where Djbh='K201301071249' group by XfID,Zssl会得到不同的结果如下:
XfID Spzs
3 2
4 1
对T_Xfzs表单独查询的结果是正确的,那么上面那条语句在哪出了问题?
用left join是包含左边表所有记录
这个 表 dbo.T_Xfxx.Djbh='K201301071249' 包含的记录比dbo.T_Xfzs.Djbh='K201301071249' 这个还多吧
这样的话上面的语名就没有错吧。上面查的是T_Xfxx,下面查的是T_Xfzs