34,838
社区成员




create table t(A varchar(10),B varchar(10))
insert into t values('1' , '3')
insert into t values('2' , '7')
insert into t values('8' , '1')
insert into t values('a' , 'sg')
insert into t values('100', '100')
insert into t values('sg' , '2')
go
select a from t where a in (select distinct b from t)
drop table t
/*
a
----------
1
2
100
sg
(所影响的行数为 4 行)
*/
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/26
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([A] varchar(3),[B] varchar(3))
insert @TB
select '1','3' union all
select '2','7' union all
select '8','1' union all
select 'a','sg' union all
select '100','100' union all
select 'sg','2'
select a
from @TB t
where exists(select 1 from @tb where b=t.a)
--测试结果:
/*
a
----
1
2
100
sg
(4 row(s) affected)
*/
select a from t where a in (select distinct b from t)
select a from t where a not in (select distinct b from t)
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-26 15:29:10
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (A varchar(3),B varchar(3))
INSERT INTO @tb
SELECT '1','3' UNION ALL
SELECT '2','7' UNION ALL
SELECT '8','1' UNION ALL
SELECT 'a','sg' UNION ALL
SELECT '100','100' UNION ALL
SELECT 'sg','2'
--SQL查询如下:
select A from @tb
intersect
select B from @tb
/*
A
----
1
100
2
sg
(4 行受影响)
*/