请教：SQL如何去除重复记录的行（两个字段拼在一起才算重复）
本帖最后由 just4 于 2013-08-16 08:59:44 编辑 请教：SQL如何去除重复记录的行（两个字段拼在一起才算重复）

比如：
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

SQL;2个字段;重复记录
[解决办法]

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)
*/

[解决办法]
方法2,

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, 
 
         row_number() over(partition by case when f1<=f2 then f1+f2 else f2+f1 end 
                           order by getdate()) 'rn'
  from #1) t
 where t.rn>1
 
select * from #1

/*
f1   f2
---- ----
x1   x2
x3   x4
x5   x6

(3 row(s) affected)
*/