CREATE TABLE #T(LOTNO INT,QTY NUMERIC(18, 8))
......SELECT * FROM T LEFT JOIN (SELECT LOTNO, SUM(QTY) AS QTY
FROM #T
GROUP BY LOTNO) A
ON T.LOT_NO = LOTNO
WHERE T.LOT_QTY-ISNULL(QTY,0)>0DROP TABLE #T
-----------------------------------------------------------------
在本機運行得到一條紀錄(正確),但在客戶的電腦運行得到三條紀錄(錯誤).
本機和客戶端的數據是相同的,奇怪的是在客戶端WHERE 語句
T.LOT_QTY-ISNULL(QTY,0)>0 不知伸麼原因不起作用,改成T.LOT_QTY-ISNULL(QTY,0)>1000 或T.LOT_QTY-ISNULL(QTY,0)<1000...或者是刪掉都是一樣的結果.
謝謝你的寶貴意見!
......SELECT * FROM T LEFT JOIN (SELECT LOTNO, SUM(QTY) AS QTY
FROM #T
GROUP BY LOTNO) A
ON T.LOT_NO = LOTNO
WHERE T.LOT_QTY-ISNULL(QTY,0)>0DROP TABLE #T
-----------------------------------------------------------------
在本機運行得到一條紀錄(正確),但在客戶的電腦運行得到三條紀錄(錯誤).
本機和客戶端的數據是相同的,奇怪的是在客戶端WHERE 語句
T.LOT_QTY-ISNULL(QTY,0)>0 不知伸麼原因不起作用,改成T.LOT_QTY-ISNULL(QTY,0)>1000 或T.LOT_QTY-ISNULL(QTY,0)<1000...或者是刪掉都是一樣的結果.
謝謝你的寶貴意見!
指定在对空值使用等于 (=) 和不等于 (<>) 比较运算符时,这些运算符的 SQL-92 遵从行为。
下例使用等于 (=) 和不等于 (<>) 比较运算符对表中的 NULL 值和非空值进行比较。下例还显示 IS NULL 不受 SET ANSI_NULLS 设置的影响。-- Create table t1 and insert values.
CREATE TABLE t1 (a int null)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO
-- Print message and perform SELECT statements.
PRINT 'Testing default setting'
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL
SELECT *
FROM t1
WHERE a = @varname
SELECT *
FROM t1
WHERE a <> @varname
SELECT *
FROM t1
WHERE a IS NULL
GO
-- Drop table t1.
DROP TABLE t1
GO
如果还有问题,建议把没有条件的数据追加到临时表中,再检查这些数据是否有问题
SELECT * into #ttFROM T
LEFT JOIN (SELECT LOTNO, SUM(QTY) AS QTY
FROM #T
GROUP BY LOTNO) A
ON T.LOT_NO = LOTNO
但是還不知道是伸麼問題:)SELECT T.LOT_QTY-ISNULL(QTY,0) AS QTY
into #tt
FROM T
LEFT JOIN (SELECT LOTNO, SUM(QTY) AS QTY
FROM #T
GROUP BY LOTNO) A
ON T.LOT_NO = LOTNOSELECT * FROM #tt WHERE QTY>0
FROM T
WHERE T.LOT_QTY is null