數據庫環境：SQL SERVER 2008R2

需求如題，左圖為初始數據，右圖是已實現需求的數據展示

簡單說下我的實現思路

1.按id、name排序給原始數據生成行號

2.用遞歸判斷上下行的id是否相等，第一次出現計數器初始值為1，后面再出現則計數器+1

3.對步驟2中生成的結果集再處理，計數器為1的id不變，計數器大于1則id為空字符串

我把實現的代碼貼出來

/*準備基礎數據*/WITH    x0          AS ( SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   1 AS id ,                        'b' AS NAME               UNION ALL               SELECT   1 AS id ,                        'c' AS NAME               UNION ALL               SELECT   2 AS id ,                        'e' AS NAME               UNION ALL               SELECT   2 AS id ,                        'd' AS NAME               UNION ALL               SELECT   3 AS id ,                        'f' AS NAME               UNION ALL               SELECT   4 AS id ,                        'h' AS NAME               UNION ALL               SELECT   4 AS id ,                        'j' AS NAME             ),/*按id、name排序生成行號*/        x1          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY id, name ) AS tid ,                        CAST(id AS VARCHAR(2)) id ,                        name               FROM     x0             ),/*遞歸設置計數器*/        x2 ( tid, id, name, level )          AS ( SELECT   tid ,                        id ,                        name ,                        1 AS level               FROM     x1               WHERE    tid = 1               UNION ALL               SELECT   t1.tid ,                        t1.id ,                        t1.NAME ,                        CASE WHEN t1.id = t2.id THEN level + 1                             ELSE 1                        END level               FROM     x1 t1                        INNER JOIN x2 t2 ON t1.tid = t2.tid + 1             )    /*計數器為1則id不動，否則置為空字符串*/    SELECT  CASE WHEN level = 1 THEN id                 ELSE ''            END id ,            name    FROM    x2

先比我的實現，有一網友提供了更簡單的實現方式

我們來看一下他是怎么實現的

WITH    x0          AS ( SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   1 AS id ,                        'b' AS NAME               UNION ALL               SELECT   1 AS id ,                        'c' AS NAME               UNION ALL               SELECT   2 AS id ,                        'e' AS NAME               UNION ALL               SELECT   2 AS id ,                        'd' AS NAME               UNION ALL               SELECT   3 AS id ,                        'f' AS NAME               UNION ALL               SELECT   4 AS id ,                        'h' AS NAME               UNION ALL               SELECT   4 AS id ,                        'j' AS NAME               UNION ALL               SELECT   1 AS id ,                        'j' AS NAME             )    SELECT  REPLACE(CASE WHEN ROW_NUMBER() OVER ( PARTITION BY CAST(ID AS VARCHAR(2)) ORDER BY NAME ) <> '1'                         THEN 0                         ELSE CAST(ID AS VARCHAR(20))                    END, 0, '') AS ID ,            NAME    FROM    x0

實現的思路和我一樣，但他的方法比我的簡單，也容易理解。

我相信，實現該需求的方法不局限于這2種，歡迎各位看官提出更多的解題方法。

（本文完）