Oracle之多行记录变一行记录，行变列，并排序（wmsys.wm_concat）

喜欢ヅ旅行 2021-06-26 16:06 1014阅读 0赞

## `wmsys.wm_concat` ##

定义：Oracle PL / SQL `WM_CONCAT`函数用于将来自多个行的数据聚合为单个行，从而提供与特定`COMMENT_BODY`关联的数据列表。实际上，它将以逗号分隔的列表进行交叉制表。

请注意，`WM_CONCAT`未文档化且不受Oracle支持，这意味着它不应在生产系统中使用。 `LISTAGG`函数可以生成与`WM_CONCAT`相同的输出，并由Oracle记录和支持。

## 1. 现有数据结构 ##

**BBSDETAIL表（主表）**

DETAIL_ID NOT NULL NUMBER　　主键
    TITLE NOT NULL VARCHAR2(100)

**BBSCOMMENT表（从表）**

DETAIL_ID NOT NULL NUMBER　　外键
    COMMENT_BODY NOT NULL VARCHAR2(500)
    COMMENT_TIME NOT NULL DATE

## 2. 实现功能一（从表多行记录合并为一行，不要求排序） ##

--多行合并为一行，不要求排序
    select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}')
    from BBSCOMMENT
    group by DETAIL_ID;

输出：

13859 {东西好，送货快 },{好 },{物流有些慢 }
    14938 {卖家还是挺热心的，以后再来 },{东西不错 }

## 3. 实现功能二（从表多行记录合并为一行后，与主表做一连接） ##

--将上述SQL语句与主表做一个连接查询
    select bd.DETAIL_ID,TITLE,bcm.COMMENT_INFO
    from BBSDETAIL bd,(select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}') as COMMENT_INFO from BBSCOMMENT group by DETAIL_ID) bcm
    where bd.DETAIL_ID=bcm.DETAIL_ID(+);

输出：

13859 苏泊尔电压力锅配件 {东西好，送货快 },{好 },{物流有些慢 }
    14938 Nike/耐克男性跑步鞋跑步 {卖家还是挺热心的，以后再来 },{东西不错 }

## 4. 实现功能三（从表多行记录合并为一行，并按评价时间排序） ##

--多行合并为一行，要求排序（最新的评论在前面）
    select DETAIL_ID, max(r)
    from (select DETAIL_ID, wmsys.wm_concat(COMMENT_BODY||'('||to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss')||')')
     OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) r from BBSCOMMENT)
    group by DETAIL_ID;

输出：

13859 东西好，送货快(2013-02-19 06:27:37),好(2012-01-14 02:23:46),物流有些慢(2012-01-01 12:00:25)
    14938 卖家还是挺热心的，以后再来(2011-11-27 05:28:27),东西不错(2011-10-11 05:09:06)

## 5. 实现功能四（行变列：分两列显示从表两种汇总结果，排序，并保证两列中数据的对应关系） ##

--分两列显示两种汇总结果，并排序，保证对应关系
    select DETAIL_ID,COMMENT_TIME,COMMENT_BODY from (
     select
     DETAIL_ID,
      WMSYS.WM_CONCAT(to_char(COMMENT_TIME,'yyyy-mm-dd hh:mi:ss'))
              OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_TIME,
       WMSYS.WM_CONCAT('{'||COMMENT_BODY||'}')
              OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME) COMMENT_BODY,
          row_number() OVER(PARTITION BY DETAIL_ID ORDER BY COMMENT_TIME desc) rs
      from BBSCOMMENT) where rs=1;

输出：

13859 2013-02-19 06:27:37,2012-01-14 02:23:46,2012-01-01 12:00:25 {东西好，送货快},{好},{物流有些慢}
    14938 2011-11-27 05:28:27,2011-10-11 05:09:06 {卖家还是挺热心的，以后再来},{东西不错}