我有一个包含两列并包含两个记录的表。
SQL表结构:
CREATE TABLE #TEMP_TEST
(
ID bigint NOT NULL,
DESCRIPTION varchar(500) NULL
) ON [PRIMARY]
INSERT INTO #TEMP_TEST VALUES(1, 'obsolete')
INSERT INTO #TEMP_TEST VALUES(2, NULL)
SELECT * FROM #TEMP_TEST
#TEMP_TEST
表中的虚拟记录总数
ID | DESCRIPTION |
--------+-------------+
1 | obsolete |
2 | NULL |
查询这些条件=
,like
,in
工作正常。
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION ='obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION IN ('obsolete')
这些结果正常工作:
ID | DESCRIPTION |
--------+-------------+
1 | obsolete |
问题:
查询这些条件!=
,not like
,not in
不能正常工作。
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION != 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT LIKE 'obsolete'
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT IN ('obsolete')
结果:
ID | DESCRIPTION |
--------+-------------+
预期成绩:
ID | DESCRIPTION |
--------+-------------+
2 | NULL |
我应该怎么做才能获得预期的结果?
如果需要进一步的条件以取得预期的结果,请检查并给出所需的答案或评论。
您可以使用OR
,如果它会甚至工作ANSI_NULLS
是ON
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION != 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT LIKE 'obsolete' OR LC2.DESCRIPTION IS NULL
SELECT * FROM #TEMP_TEST LC2 WHERE LC2.DESCRIPTION NOT IN ('obsolete') OR LC2.DESCRIPTION IS NULL
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句