麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
737
2023-04-28
将一列中多行相同的值只显示在一行
数据库环境: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 , 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种,欢迎各位看官提出更多的解题方法。
(本文完)
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。