举例:
A表记录
字段名 item_no
12
22
34
44
B表记录
字段名 key2
12
12
34
55
66
想得出这样的结果
22
44
55
66
说明:查出的结果是A表和B表都不同时存在的记录,A表中item_no是没有重复的,但是B表中key2字段是可能有重复值的请高手解答
A表记录
字段名 item_no
12
22
34
44
B表记录
字段名 key2
12
12
34
55
66
想得出这样的结果
22
44
55
66
说明:查出的结果是A表和B表都不同时存在的记录,A表中item_no是没有重复的,但是B表中key2字段是可能有重复值的请高手解答
FROM tb1 AS A
FULL JOIN tb2 AS B
ON A.key1=B.key2
WHERE A.key1 IS NULL OR B.key2 IS NULL
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-15 10:50:30
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (item_no INT)
INSERT INTO @tb1
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 34 UNION ALL
SELECT 44
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (key2 INT)
INSERT INTO @tb2
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 34 UNION ALL
SELECT 55 UNION ALL
SELECT 66--SQL查询如下:SELECT ISNULL(A.item_no,B.key2) AS [key]
FROM @tb1 AS A
FULL JOIN @tb2 AS B
ON A.item_no=B.key2
WHERE A.item_no IS NULL OR B.key2 IS NULL/*
key
-----------
22
44
55
66(4 行受影响)*/
select * from a union select * from b) t
where item_no not in (select item from a where item in(select key2 from b ))
select * from a union select * from b) t
where item_no not in (select item from a where item in(select key2 from b ))
DECLARE @tb1 TABLE (item_no INT)
INSERT INTO @tb1
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 34 UNION ALL
SELECT 44
DECLARE @tb2 TABLE (key2 INT)
INSERT INTO @tb2
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 34 UNION ALL
SELECT 55 UNION ALL
SELECT 66select *
from
(select * from @tb1 union select * from @tb2) t
where item_no not in (select item_no from @tb1 where item_no in(select key2 from @tb2 ))/*
item_no
-----------
22
44
55
66(4 行受影响)
*/
insert A11
select 12 union all
select 22 union all
select 34 union all
select 44
create table b11 (key2 int)
insert b11
select 12 union all
select 12 union all
select 34 union all
select 55 union all
select 66
select * from A11 a where not exists(select * from b11 b where a.item_no =b.key2 )
union
select * from b11 b where not exists(select * from a11 a where a.item_no =b.key2 )
FROM tb1 AS A
FULL JOIN tb2 AS B
ON A.key1 = B.key2
WHERE A.key1 IS NULL OR B.key2 IS NULL
同意小梁的
DECLARE @tb1 TABLE (item_no INT)
INSERT INTO @tb1
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 34 UNION ALL
SELECT 44
DECLARE @tb2 TABLE (key2 INT)
INSERT INTO @tb2
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 34 UNION ALL
SELECT 55 UNION ALL
SELECT 66select *
from (select * from @tb1 union select * from @tb2) t
where item_no not in (select item_no from @tb1 ,@tb2 where item_no=key2)/*
item_no
-----------
22
44
55
66(4 行受影响)
*/
DECLARE @tb1 TABLE (item_no INT)
INSERT INTO @tb1
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 34 UNION ALL
SELECT 44
DECLARE @tb2 TABLE (key2 INT)
INSERT INTO @tb2
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 34 UNION ALL
SELECT 55 UNION ALL
SELECT 66select * from @tb1 t where not exists(select * from @tb2 where key2=t.item_no)
union all
select * from @tb2 t where not exists(select * from @tb1 where item_no=t.key2)/*
item_no
-----------
22
44
55
66(4 行受影响)
*/
create table tt11
(
item_no int
)
create table tt22
(
key2 int
)
insert into tt11
values
(12),(22),(34),(44)
insert into tt22
values
(12),(12),(34),(55),(66)(
select *
from tt11
except
select *
from tt22
)
union
(
select *
from tt22
except
select *
from tt11
)/*
item_no
22
44
55
66
*/
MINUS
select * from b11
except
select *from b11)
union
(select *from b11
except
select * from a11)
DECLARE @tb1 TABLE (item_no INT)
INSERT INTO @tb1
SELECT 12 UNION ALL
SELECT 22 UNION ALL
SELECT 34 UNION ALL
SELECT 44
DECLARE @tb2 TABLE (key2 INT)
INSERT INTO @tb2
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 34 UNION ALL
SELECT 55 UNION ALL
SELECT 66
(select * from @tb2
except
select * from @tb1)
union all
(select * from @tb1
except
select * from @tb2 )/*
item_no
-----------
22
44
55
66(4 行受影响)
*/
select item_no,0 'b' from a
union
select item_no, 1 'b' from b
) ts
group by item_no
having count(*) = 1