27,580
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([NS] [nvarchar](10),[NNS] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A','B' UNION ALL
SELECT 'B','A' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'A','D' UNION ALL
SELECT 'C','B' UNION ALL
SELECT 'A','B' UNION ALL
SELECT 'B','E'
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY CASE WHEN NS<NNS THEN NS ELSE NNS END,CASE WHEN NS<NNS THEN NNS ELSE NS END ORDER BY @@spid),*
FROM tb
)
DELETE t WHERE rn>1
SELECT * FROM tb
/*
NS NNS
---------- ----------
A B
A D
C B
B E
(4 行受影响)
*/