Oracle之多行记录变一行记录,行变列,并排序(wmsys.wm_concat)

wmsys.wm_concat

Definition: The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific COMMENT_BODY. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by 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 {卖家还是挺热心的,以后再来},{东西不错}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值