-- ============================================= -- Author: T.O.P -- Create date: 2009/11/27 -- Version: SQL SERVER 2005 -- ============================================= declare @tb1 table([tb1Column1] varchar(1),[tb1Column2] int,[tb1Column3] varchar(2)) insert @tb1 select 'a',1,'a1' union all select 'a',2,'a2' union all select 'a',3,'a9' union all select 'a',3,'a4' union all select 'a',4,'a5' union all select 'b',1,'b1' union all select 'b',1,'b4' union all select 'b',1,'b6' union all select 'b',1,'b1'declare @tb2 table([tb2Column1] varchar(2),[tb2Column2] varchar(1),[tb2Column3] varchar(2)) insert @tb2 select 'xx','a','a1' union all select 'xx','a','a2' union all select 'xx','a','a3' union all select 'xx','a','a8' union all select 'xx','a','a5' union all select 'cc','b','b2' union all select 'cc','b','b4' union all select 'cc','b','b8' union all select 'cc','b','b7'--tb1Column3,[tb2Column3]同时包含的 select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] intersect select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] union select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] intersect select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] --tb1Column3,[tb2Column3]不同时包含的 select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] except select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] union select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] except select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]--测试结果: /*--tb1Column3,[tb2Column3]包含的 tb1Column3 ---------- a1 a2 a5 b4(4 row(s) affected)--tb1Column3,[tb2Column3]不同时包含的 tb1Column3 ---------- a3 a8 b2 b7 */
-- ============================================= -- Author: T.O.P -- Create date: 2009/11/27 -- Version: SQL SERVER 2005 -- ============================================= declare @tb1 table([tb1Column1] varchar(1),[tb1Column2] int,[tb1Column3] varchar(2)) insert @tb1 select 'a',1,'a1' union all select 'a',2,'a2' union all select 'a',3,'a9' union all select 'a',3,'a4' union all select 'a',4,'a5' union all select 'b',1,'b1' union all select 'b',1,'b4' union all select 'b',1,'b6' union all select 'b',1,'b1'declare @tb2 table([tb2Column1] varchar(2),[tb2Column2] varchar(1),[tb2Column3] varchar(2)) insert @tb2 select 'xx','a','a1' union all select 'xx','a','a2' union all select 'xx','a','a3' union all select 'xx','a','a8' union all select 'xx','a','a5' union all select 'cc','b','b2' union all select 'cc','b','b4' union all select 'cc','b','b8' union all select 'cc','b','b7' SELECT T1.[tb1Column3], '同时包含' FROM ( --tb1Column3,[tb2Column3]同时包含的 select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] intersect select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] union select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] intersect select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] ) T1 UNION ALL SELECT T2.[tb1Column3], '不同时包含' FROM ( --tb1Column3,[tb2Column3]不同时包含的 select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] except select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] union select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] except select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2] ) T2--测试结果: /* tb1Column3 ---------- ---------- a1 同时包含 a2 同时包含 a5 同时包含 b4 同时包含 a3 不同时包含 a8 不同时包含 b2 不同时包含 b7 不同时包含 b8 不同时包含(9 row(s) affected) */
是不是这个意思,我想可以不是哦
what mean?
tb2.tb2Column2=tb1.tb1Column1
找出tb2里的tb2.tb2Column3与tb1.tb1Column3不同和相同的行
-- Author: T.O.P
-- Create date: 2009/11/27
-- Version: SQL SERVER 2005
-- =============================================
declare @tb1 table([tb1Column1] varchar(1),[tb1Column2] int,[tb1Column3] varchar(2))
insert @tb1
select 'a',1,'a1' union all
select 'a',2,'a2' union all
select 'a',3,'a9' union all
select 'a',3,'a4' union all
select 'a',4,'a5' union all
select 'b',1,'b1' union all
select 'b',1,'b4' union all
select 'b',1,'b6' union all
select 'b',1,'b1'declare @tb2 table([tb2Column1] varchar(2),[tb2Column2] varchar(1),[tb2Column3] varchar(2))
insert @tb2
select 'xx','a','a1' union all
select 'xx','a','a2' union all
select 'xx','a','a3' union all
select 'xx','a','a8' union all
select 'xx','a','a5' union all
select 'cc','b','b2' union all
select 'cc','b','b4' union all
select 'cc','b','b8' union all
select 'cc','b','b7'--tb1Column3,[tb2Column3]同时包含的
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
intersect
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
union
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
intersect
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
--tb1Column3,[tb2Column3]不同时包含的
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
except
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
union
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
except
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]--测试结果:
/*--tb1Column3,[tb2Column3]包含的
tb1Column3
----------
a1
a2
a5
b4(4 row(s) affected)--tb1Column3,[tb2Column3]不同时包含的
tb1Column3
----------
a3
a8
b2
b7
*/
-- Author: T.O.P
-- Create date: 2009/11/27
-- Version: SQL SERVER 2005
-- =============================================
declare @tb1 table([tb1Column1] varchar(1),[tb1Column2] int,[tb1Column3] varchar(2))
insert @tb1
select 'a',1,'a1' union all
select 'a',2,'a2' union all
select 'a',3,'a9' union all
select 'a',3,'a4' union all
select 'a',4,'a5' union all
select 'b',1,'b1' union all
select 'b',1,'b4' union all
select 'b',1,'b6' union all
select 'b',1,'b1'declare @tb2 table([tb2Column1] varchar(2),[tb2Column2] varchar(1),[tb2Column3] varchar(2))
insert @tb2
select 'xx','a','a1' union all
select 'xx','a','a2' union all
select 'xx','a','a3' union all
select 'xx','a','a8' union all
select 'xx','a','a5' union all
select 'cc','b','b2' union all
select 'cc','b','b4' union all
select 'cc','b','b8' union all
select 'cc','b','b7'
SELECT T1.[tb1Column3], '同时包含'
FROM (
--tb1Column3,[tb2Column3]同时包含的
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
intersect
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
union
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
intersect
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
) T1
UNION ALL
SELECT T2.[tb1Column3], '不同时包含'
FROM (
--tb1Column3,[tb2Column3]不同时包含的
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
except
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
union
select b.[tb2Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
except
select a.[tb1Column3] from @tb1 a inner join @tb2 b on a.[tb1Column1] = b.[tb2Column2]
) T2--测试结果:
/*
tb1Column3
---------- ----------
a1 同时包含
a2 同时包含
a5 同时包含
b4 同时包含
a3 不同时包含
a8 不同时包含
b2 不同时包含
b7 不同时包含
b8 不同时包含(9 row(s) affected)
*/