A 表中
id name
1 12
2 23
B 表中
id vname
1 12
2 34
查A表中 name在B表中vname中不存在的
select name from A where name not in(select vname from B );
这样写不对吗??我怎么查不出来啊?
id name
1 12
2 23
B 表中
id vname
1 12
2 34
查A表中 name在B表中vname中不存在的
select name from A where name not in(select vname from B );
这样写不对吗??我怎么查不出来啊?
EXCEPT
SELECT * FROM B
where name not in(select vname from B WHERE VNAME IS NOT NULL); 有NULL值吧
from a
except
select *
from b
where not exists(select 1 from b where lrtrim(rtrim(a.name))=lrtrim(rtrim(b.name)))
where not exists(select 1 from b where ltrim(rtrim(a.name))=ltrim(rtrim(b.name)))
-- id name
-- 1 12
-- 2 23
--B 表中
-- id vname
-- 1 12
-- 2 34
DECLARE @A TABLE (ID INT PRIMARY KEY IDENTITY(1,1),name VARCHAR(2));INSERT INTO @A SELECT '12'
UNION ALL
SELECT '23'
DECLARE @B TABLE (ID INT PRIMARY KEY IDENTITY(1,1),vname VARCHAR(2));INSERT INTO @B SELECT '12'
UNION ALL SELECT '34'SELECT * FROM @A
EXCEPT
SELECT * FROM @B
(2 行受影响)(2 行受影响)
ID name
----------- ----
2 23(1 行受影响)
我就想得A表中name字段的23这个值在B表中不存在
2 12
1 23
except会把两行记录全部显示,而不是LZ希望的只显示
X 23
-- id name
-- 1 12
-- 2 23
--B 表中
-- id vname
-- 1 12
-- 2 34
DECLARE @A TABLE (ID INT PRIMARY KEY IDENTITY(1,1),name VARCHAR(2));INSERT INTO @A SELECT '23'
UNION ALL
SELECT '12'
DECLARE @B TABLE (ID INT PRIMARY KEY IDENTITY(1,1),vname VARCHAR(2));INSERT INTO @B SELECT '12'
UNION ALL SELECT '34'SELECT name FROM @A
EXCEPT
SELECT vname FROM @B