日期:2014-05-17 浏览次数:20552 次
--> 测试数据: #TB if object_id('tempdb.dbo.#TB') is not null drop table #TB create table #TB (id int,Uid int,Fid int,Userimp datetime,Addtime sql_variant) insert into #TB select 1,100,101,'2012-01-12',null union all select 2,100,102,'2012-01-12',null union all select 3,100,103,'2012-01-12',null union all select 4,100,104,'2012-01-12',null union all select 5,101,100,'2012-01-13',null union all select 6,101,102,'2012-01-13',null union all select 7,105,101,'2012-01-14',null --1 select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID) ---2 select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE T.UID=FID AND T.FID<>UID) AND NOT EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID) --3 select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE T.FID=UID AND T.UID<>FID) AND NOT EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID) /* (所影响的行数为 7 行) id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------- 1 100 101 2012-01-12 00:00:00.000 NULL 5 101 100 2012-01-13 00:00:00.000 NULL (所影响的行数为 2 行) id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------- 2 100 102 2012-01-12 00:00:00.000 NULL 3 100 103 2012-01-12 00:00:00.000 NULL 4 100 104 2012-01-12 00:00:00.000 NULL 6 101 102 2012-01-13 00:00:00.000 NULL (所影响的行数为 4 行) id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ --------------------