如何去除相同数据，并非distinct显示

[水晶烟灰缸]

表 t1 表 t2
id v id v
--------- ----------
1 A1 1 A1
2 A2 3 A3
3 A3 5 A5
4 A4 7 A7
5 A5 9 A9
6 A6

t1为主表
查询结果
id v
---------
2 A2
4 A4
6 A6

[Neo_whl]

select  t1.id,t1.v from t1 join t2 on t1.id<>t2.id
或
select id,v from t1 where id not in (select id from t2) 

[唐诗三百首]

-- 写法1
select a.id,a.v
 from t1 a
 left join t2 b on a.id=b.id
 where b.id is null
 
-- 写法2
select a.id,a.v
 from t1 a
 where not exists(select 1 from t2 b 
                  where b.id=a.id)

[lzw_0736]

select * from t1 where v not in (select v from t2)

[lzw_0736]

select * from t1
except
select * from t2

[--小F--]

select * from [t1] WHERE CHECKSUM(id)  NOT IN (SELECT CHECKSUM(id) FROM t2)

[jiajiaren]

IF OBJECT_ID(N't1',N'U') IS NOT NULL
DROP TABLE t1
GO

CREATE TABLE t1(id INT IDENTITY(1,1)  PRIMARY KEY,v VARCHAR(10))
INSERT INTO dbo.t1 SELECT 'A1' 
UNION ALL SELECT 'A2'
UNION ALL SELECT 'A3'
UNION ALL SELECT 'A4'
UNION ALL SELECT 'A5'
UNION ALL SELECT 'A6'        

IF OBJECT_ID(N't2',N'U') IS NOT NULL
DROP TABLE t2
GO
CREATE TABLE t2(id INT  PRIMARY KEY,v VARCHAR(10))
INSERT INTO t2 SELECT 1,'A1' 
UNION ALL SELECT 3,'A3' 
UNION ALL SELECT 5,'A5'
UNION ALL SELECT 7,'A7'   
UNION ALL SELECT 9,'A9'

 ---------------------------------------------查询-------------------------------------------
 
SELECT * FROM  t1  WHERE id NOT IN (SELECT  a.id FROM  t1 a INNER JOIN t2 b ON a.id=b.id AND a.v=b.v)

SELECT * FROM  t1 a WHERE   NOT EXISTS (SELECT  id FROM  t2 b WHERE a.id=b.id AND a.v=b.v)
 
SELECT   a.* FROM   t1 a LEFT JOIN t2 b ON a.id=b.id WHERE  b.id  IS NULL  AND  b.v  IS NULL 
----------------------------------------------结果--------------------------------------------
/*
id          v
----------- ----------
2           A2
4           A4
6           A6

(3 行受影响)

id          v
----------- ----------
2           A2
4           A4
6           A6

(3 行受影响)

id          v
----------- ----------
2           A2
4           A4
6           A6

(3 行受影响)

*/

[七不语v]

学习。。

[tcmakebest]

高人真多啊，except 很好用

[yoan2014]

SQLSERVER05以后还可以用

IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE o.name= 'test')
	DROP TABLE test
GO
CREATE TABLE test(id INT , v VARCHAR(02))
GO
INSERT INTO test
SELECT 1 , 'A1' UNION ALL
SELECT 2 , 'A2' UNION ALL
SELECT 3 , 'A3' UNION ALL
SELECT 4 , 'A4' UNION ALL
SELECT 5 , 'A5' UNION ALL
SELECT 6 , 'A6'

CREATE TABLE test2(id INT , v VARCHAR(02))
GO
INSERT INTO test2
SELECT 1 , 'A1' UNION ALL
SELECT 3 , 'A3' UNION ALL
SELECT 5 , 'A5' UNION ALL
SELECT 7 , 'A7' UNION ALL
SELECT 9 , 'A9'
GO

SELECT id,v FROM test EXCEPT SELECT id,v FROM test2 
/*
id          v
----------- ----
2           A2
4           A4
6           A6

(3 行受影响)

 */

[我是小数位]

引用 2 楼 ap0405140 的回复:

-- 写法1
select a.id,a.v
 from t1 a
 left join t2 b on a.id=b.id
 where b.id is null
 
-- 写法2
select a.id,a.v
 from t1 a
 where not exists(select 1 from t2 b 
                  where b.id=a.id)

强力推荐

[我是小数位]

引用 8 楼 renliquan 的回复:

这么多答案，用哪个更有效率????

这种排除一般用except 或not exists,比较容易看出你的需求

[renliquan]

这么多答案，用哪个更有效率????

[grn0515]

引用 4 楼 lzw_0736 的回复:

select * from t1
except
select * from t2

推荐