表t: 列 A B
1 3
2 7
8 1
a sg
100 100
sg 2 ..........查询结果:显示A数据(不包含B列中与A相同的数据)
结果:
-------------
A
1
2
100
sg
请问这个问题如何写sql?
1 3
2 7
8 1
a sg
100 100
sg 2 ..........查询结果:显示A数据(不包含B列中与A相同的数据)
结果:
-------------
A
1
2
100
sg
请问这个问题如何写sql?
调试欢乐多
intersect
select b from tb
-- 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 行受影响)
*/
-- 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)
*/
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')
goselect a from t where a in (select distinct b from t) drop table t/*
a
----------
1
2
100
sg(所影响的行数为 4 行)
*/