數據庫環境：SQL SERVER 2005
現有一個表的數據如下，id是主鍵，p1，p2是字符串類型，如果當前行的p1，p2字段的值分別等于其它行
的字段p2，p1的值，則視這2行記錄為一組。比如，id=1和id=5就屬于同一組數據。同一組數據只顯示id最小
的那行記錄，沒有組的數據全部顯示。
實現思路：
將表進行自關聯左聯，假設表的別名是a，b，根據id進行關聯，對關聯后的結果集進行過濾。如果b.id是空的，則保留，
如果b.id不為空，則只保留a.id比b.id小的記錄。
實現的SQL腳本：
/*1.數據準備*/WITH x0 AS ( SELECT 1 AS id , 'A' AS p1 , 'B' AS p2 /*UNION ALL SELECT 0 AS id , 'A' AS p1 , 'B' AS p2*/ UNION ALL SELECT 2 AS id , 'C' AS p1 , 'D' AS p2 UNION ALL SELECT 3 AS id , 'E' AS p1 , 'F' AS p2 UNION ALL SELECT 4 AS id , 'D' AS p1 , 'C' AS p2 UNION ALL SELECT 5 AS id , 'B' AS p1 , 'A' AS p2 UNION ALL SELECT 6 AS id , 'H' AS p1 , 'J' AS p2 UNION ALL SELECT 7 AS id , 'T' AS p1 , 'U' AS p2 UNION ALL SELECT 8 AS id , 'J' AS p1 , 'H' AS p2 /*UNION ALL SELECT 9 AS id , 'I' AS p1 , 'L' AS p2 UNION ALL SELECT 10 AS id , 'J' AS p1 , 'K' AS p2*/ ),/*2.去重*/ x1 AS ( SELECT id , p1 , p2 FROM ( SELECT id , p1 , p2 , ROW_NUMBER() OVER ( PARTITION BY p1, p2 ORDER BY id ) AS rn FROM x0 ) t WHERE rn = 1 ) /*3.求值*/ SELECT a.id , a.p1 , a.p2 FROM x1 a LEFT JOIN x1 b ON b.p1 = a.p2 AND b.p2 = a.p1 WHERE b.id IS NULL OR a.id < b.idView Code
最終實現的效果如圖：
也有網友提出通過ASCII來實現，他的實現SQL腳本如下：
WITH c1 AS ( SELECT 1 AS id , 'A' AS p1 , 'B' AS p2 /*UNION ALL SELECT 0 AS id , 'A' AS p1 , 'B' AS p2*/ UNION ALL SELECT 2 AS id , 'C' AS p1 , 'D' AS p2 UNION ALL SELECT 3 AS id , 'E' AS p1 , 'F' AS p2 UNION ALL SELECT 4 AS id , 'D' AS p1 , 'C' AS p2 UNION ALL SELECT 5 AS id , 'B' AS p1 , 'A' AS p2 UNION ALL SELECT 6 AS id , 'H' AS p1 , 'J' AS p2 UNION ALL SELECT 7 AS id , 'T' AS p1 , 'U' AS p2 UNION ALL SELECT 8 AS id , 'J' AS p1 , 'H' AS p2 /*UNION ALL SELECT 9 AS id , 'I' AS p1 , 'L' AS p2 UNION ALL SELECT 10 AS id , 'J' AS p1 , 'K' AS p2*/ ), c2 AS ( SELECT MIN(id) AS min_id FROM c1 GROUP BY ASCII(p1) + ASCII(p2) ) SELECT c1.* FROM c1
新聞熱點
疑難解答
