Oracle之多行记录变一行记录,行变列,并排序(wmsys.wm_concat)
原帖:http://www.cnblogs.com/nayitian/p/3231734.html
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 {卖家还是挺热心的,以后再来},{东西不错}
相关文章
- java -version 问题 : C:ProgramDataOracleJavajavapath;
- Oracle 11g安装步骤以及Oracle11g创建表空间和用户,并授权
- Oracle-查看oracle是否有表被锁
- 【C/C++学院】(24)Oracle数据库编程--管理oracle
- 《oracle每日一练》Oracle DBLink连接数过多的问题(Ora-02020)
- ORACLE的SPFILE与PFILE
- Java并发教程(Oracle官方资料)
- 【Oracle 集群】ORACLE DATABASE 11G RAC 知识图文详细教程之RAC 工作原理和相关组件(三)
- Oracle 四种列转行的方法
- Windows DOS窗体下Oracle 数据库的导入导出命令
- C#中使用 Oracle的事务与存储过程
- Oracle 记录插入时“Invalid parameter binding ”错误
- Atitit oracle新特性5 6 7 8 9 10 11 12 18 19 20 attilax总结 目录 1.1. :ora 20c1 1.2. Oracle Database 19c 的
- Oracle入门捷径之--结合docker快速搭建学习或者测试数据库环境
- Oracle SQL 执行计划
- Oracle 12c中SQLPlus操作使用(包含实验二ORACLE SQL*PLUS环境与查询的详细操作解释)
- 02、Oracle过滤和排序数据
- 【Mysql异构实时同步Oracle】OGG12异构同步mysql到oracle(windows mysql实时同步数据到linux oracle)详细文档