将一列中多行相同的值只显示在一行

时间:2022-01-19 10:30:05

数据库环境: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种,欢迎各位看官提出更多的解题方法。

(本文完)