1.查出来736条记录SELECT COUNT(*) AS Expr1
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)
2.查出来478条记录
SELECT COUNT(*) AS Expr1
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)AND (sbh IN
(SELECT yh_sbh
FROM gw_yh))
3.查出来0条记录,原因在哪?
SELECT COUNT(*) AS Expr1
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)AND (sbh not IN
(SELECT yh_sbh
FROM gw_yh))
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)
2.查出来478条记录
SELECT COUNT(*) AS Expr1
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)AND (sbh IN
(SELECT yh_sbh
FROM gw_yh))
3.查出来0条记录,原因在哪?
SELECT COUNT(*) AS Expr1
FROM dbo.linefile
WHERE (xmgcbh NOT LIKE '%gx%') AND (LEN(sbh) < 9)AND (sbh not IN
(SELECT yh_sbh
FROM gw_yh))
--楼主看看这个, 。
--1. NULL 对 IN(NOT IN) 查询的影响
--测试数据
DECLARE @1 TABLE(col1 int)
INSERT @1 SELECT 1
UNION ALL SELECT NULL
UNION ALL SELECT 2DECLARE @2 TABLE(col1 int)
INSERT @2 SELECT 1--查询
SELECT [@1总记录数]=COUNT(*) FROM @1
--结果: 3SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 1--在@2中插入一条NULL值
INSERT @2 SELECT NULL
SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 IN(SELECT col1 FROM @2)
--结果: 1SELECT [@1在@2表中存在的记录数]=COUNT(*) FROM @1 a
WHERE col1 NOT IN(SELECT col1 FROM @2)
--结果: 0
GO
set ANSI_NULLS為false時 就不遵從SQL-92的規定 可以對空值進行比較操作
set ANSI_NULLS off后就解决了吧!
任何值跟null比较都是为null
SET ANSI_NULLS Off可以解决你的问题!
这第三句的结果就是1-2的结果吧,你还来句sql去算?