你的位置:首页 > 数据库

[数据库]将一列中多行相同的值只显示在一行


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

(本文完)