日期:2014-05-17 浏览次数:20763 次
比如:
SELECT 'x1' AS f1,'x2' AS f2
INTO #1
UNION ALL
SELECT 'x1','x2'
UNION ALL
SELECT 'x2','x1'
UNION ALL
SELECT 'x3','x4'
UNION ALL
SELECT 'x5','x6'
UNION ALL
SELECT 'x6','x5'
SELECT * FROM #1
DROP TABLE #1
f1和f2拼在一起才算重复,这样
x1,x2与x1,x2,与x2,x1都是重复记录
x5,x6与x6,x5也是重复记录
如何将复复记录去掉,保留第一行记录呢,即结果:
x1,x2
x3,x4
x5,x6
SELECT 'x1' AS f1,'x2' AS f2
INTO #1
UNION ALL
SELECT 'x1','x2'
UNION ALL
SELECT 'x2','x1'
UNION ALL
SELECT 'x3','x4'
UNION ALL
SELECT 'x5','x6'
UNION ALL
SELECT 'x6','x5'
select distinct f3 'f1',f4 'f2'
into #2
from
(select case when f1<=f2 then f1 else f2 end 'f3',
case when f1<=f2 then f2 else f1 end 'f4'
from #1) t
truncate table #1
insert into #1 select * from #2
select * from #1
/*
f1 f2
---- ----
x1 x2
x3 x4
x5 x6
(3 row(s) affected)
*/
SELECT 'x1' AS f1,'x2' AS f2
INTO #1
UNION ALL
SELECT 'x1','x2'
UNION ALL
SELECT 'x2','x1'
UNION ALL
SELECT 'x3','x4'
UNION ALL
SELECT 'x5','x6'
UNION ALL
SELECT 'x6','x5'
delete t
from
(select f1,f2,