db2 实现oracle wm_concat函数的方法-db2开发系列（一）

来源：互联网发布：人工智能框架编辑：程序博客网时间：2024/05/16 02:56

引言

大家在做oracle开发的时候，都知道有一个函数wm_concat ，它可以根据某一字段分组把其他字段多行数据转1列，即分组的字段字段1字段2...等等一条数据。最近db2里面有个统计某个菜单点击量的需求，要求把点击次数最多以及工号显示出来，当点击次数相同时，需要把那些工号用逗号链接显示在一行。还有给定任意表名批量获得( select 所有字段from 表名 )的语句 （非*）这两种都需要这个函数。

实现

1 原始数据

SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name                      ,CASE                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'                          ELSE colname                       END AS colname                  FROM SYSCAT.COLUMNS                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =                          lower ('xx.xxxxx')                ORDER BY COLNO

原始数据本身是个查询是从系统SYSCAT.COLUMNS 字典里取模式名.表名和where 条件匹配上的表的小写全名和所有的字段名。其中字符型和日期字段做了下处理，查询

结果如下：第一列表名，第二列字段名

2 先把要转换的字段名变成xml 元素

SELECT full_tab_name,xmlelement (NAME a, colname || ',')          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name                      ,CASE                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'                          ELSE colname                       END AS colname                  FROM SYSCAT.COLUMNS                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =                          lower ('xx.xxxxx')                ORDER BY COLNO               ) x          with ur;

如下：所有字段都套在<A></A>标签里面了，就和xml的一个元素一样

xmlelement(NAME a,colname ||',') Name a 是固定格式，colname 代表from 后面的字段别名，|| ',' 表示字段名后面加上个逗号

3 用xmlagg函数把分组后的字段连接

SELECT full_tab_name,xmlagg (xmlelement (NAME a, colname || ','))              ,xml2clob (xmlagg (xmlelement (NAME a, colname || ',')))                  AS col_str          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name                      ,CASE                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'                          ELSE colname                       END AS colname                  FROM SYSCAT.COLUMNS                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =                          lower ('xxx.xxxxxxxx')                ORDER BY COLNO               ) x         group by full_tab_name with ur;

如下图，一表已变成一条数据

4 替换<A></A> 标签

SELECT full_tab_name              ,replace (                  replace (                     xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),                     '<A>',                     ''),                  '</A>',                  '')                  AS col_str          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name                      ,CASE                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'                          ELSE colname                       END AS colname                  FROM SYSCAT.COLUMNS                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =                          lower 'xxx.xxxxxx')                ORDER BY COLNO               ) x         group by full_tab_name with ur;

结果如下：

5 获得语句

SELECT 'select ' || substr (col_str, 1, length (col_str) - 1) || ' from ' || full_tab_name  FROM (SELECT full_tab_name              ,replace (                  replace (                     xml2clob (xmlagg (xmlelement (NAME a, colname || ','))),                     '<A>',                     ''),                  '</A>',                  '')                  AS col_str          FROM (SELECT lower (rtrim (tabschema) || '.' || TABNAME) as full_tab_name                      ,CASE                          WHEN typename = 'VARCHAR' OR TYPENAME = 'CHARACTER' THEN 'rtrim(' || colname || ')'                          WHEN typename = 'TIMESTAMP' THEN 'to_char(' || colname || ',''YYYY-MM-DD HH24:MI:SS'')'                          ELSE colname                       END AS colname                  FROM SYSCAT.COLUMNS                 WHERE lower (rtrim (tabschema) || '.' || TABNAME) =                          lower ('xxx.xxxxx')                ORDER BY COLNO               ) x         group by full_tab_name       ) t WITH UR

0 0