SQL查询找错误

[Dream_Hunter_]

SELECT p_Name,un_Name,lastNum,lastBadNum,ISNULL(inNum,0) inNum,ISNULL(inMoney,0) inMoney,ISNULL(outNum,0) outNum,ISNULL(outMoney,0) outMoney,endNum,endBadNum,ISNULL(lastNum+inNum-outNum-endNum,0) AS balance FROM Products p LEFT JOIN
(
SELECT p_ID,
(SELECT ps_GoodNum FROM ProductStore ps WHERE ps.p_ID=bps.p_ID AND ps.ps_Date=(SELECT MIN(sps.ps_Date) FROM ProductStore sps WHERE sps.ps_Date>='2007-4-1' AND sps.p_ID=ps.p_ID)) lastNum,
(SELECT ps_BadNum FROM ProductStore ps WHERE ps.p_ID=bps.p_ID AND ps.ps_Date=(SELECT MIN(sps.ps_Date) FROM ProductStore sps WHERE sps.ps_Date>='2007-4-1' AND sps.p_ID=ps.p_ID)) lastBadNum,
(SELECT SUM(bp_Num) FROM BuyProduct bp WHERE bp.p_ID=bps.p_ID AND bp_Date>='2007-4-1' AND bp_Date<'2007-5-1') inNum,
(SELECT SUM(bp_TotalPrice) FROM BuyProduct bp WHERE bp.p_ID=bps.p_ID AND bp_Date>='2007-4-1' AND bp_Date<'2007-5-1') inMoney,
(SELECT SUM(sp_Num) FROM SellProduct sp WHERE sp.p_ID=bps.p_ID AND sp_Date>='2007-4-1' AND sp_Date<'2007-5-1') outNum,
(SELECT SUM(sp_TotalPrice) FROM SellProduct sp WHERE sp.p_ID=bps.p_ID AND sp_Date>='2007-4-1' AND sp_Date<'2007-5-1') outMoney,
(SELECT ps_GoodNum FROM ProductStore ps WHERE ps.p_ID=bps.p_ID AND ps.ps_Date=(SELECT MAX(sps.ps_Date) FROM ProductStore sps WHERE sps.ps_Date<'2007-5-1' AND sps.p_ID=ps.p_ID)) endNum,
(SELECT ps_BadNum FROM ProductStore ps WHERE ps.p_ID=bps.p_ID AND ps.ps_Date=(SELECT MAX(sps.ps_Date) FROM ProductStore sps WHERE sps.ps_Date<'2007-5-1' AND sps.p_ID=ps.p_ID)) endBadNum
FROM ProductStore bps
GROUP BY p_ID
) t
ON p.p_ID=t.p_ID
INNER JOIN Unit un
ON p.un_ID=un.un_ID

上面这段不知道错在哪啊。请大家帮忙看看。
用法和下面一样，但balance就是不对。

SELECT A AS A,B AS B ,A-B AS C FROM 
(SELECT 2 AS A,1 AS B UNION SELECT 3 AS A,4 AS B) T

[jiangshun]

报什么错啊？

[jack15850798154]

balance是一个汇总的值你换成别的别名看看行吗？你也可以把这么长的语句分成各个小语句来测试一下，看看如何？不对的地方请多多指教啊！！

[kylin011]

飘过！！！

[cyrix_wxm]

没错吧！
数据有问题？

[silentwins]

没有这个字段就不出错么？

也不描述一下，搞不懂……

[zzxap]

ISNULL(lastNum+inNum-outNum-endNum,0) AS balance


lastNum+inNum-outNum-endNum 怎么会是null呢?

[wuyi8808]

在查询分析器执行一下看看。

[Dream_Hunter_]

[Quote=引用 6 楼 zzxap 的回复:]
ISNULL(lastNum+inNum-outNum-endNum,0) AS balance


lastNum+inNum-outNum-endNum 怎么会是null呢?
[/Quote]
inNum和outNum可能是null的。
楼上的各位，没说清楚。不好意思。
运行是不报错的，但是结果不对。就是balance那个字段。结果和对应的行的lastNum+inNum-outNum-endNum结果不等。

[jwdream2008]

TRY:

(ISNULL(lastNum,0)+ISNull(inNum,0)-ISNULL(outNum,0)-ISNULL(endNum,0)) AS balance 

[Dream_Hunter_]

[Quote=引用 9 楼 jwdream2008 的回复:]
TRY:
SQL code(ISNULL(lastNum,0)+ISNull(inNum,0)-ISNULL(outNum,0)-ISNULL(endNum,0))AS balance
[/Quote]

正确。
这个是为什么？

[Dream_Hunter_]

知道了。谢谢！～null参与运算就得null