Oracle之多行记录变一行记录,行变列,并排序(wmsys.wm_concat)
来源:互联网 发布:好用的windows平板 编辑:程序博客网 时间:2024/06/10 03:18
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 BBSCOMMENTgroup by DETAIL_ID;
输出:
13859 {东西好,送货快 },{好 },{物流有些慢 }
14938 {卖家还是挺热心的,以后再来 },{东西不错 }
3. 实现功能二(从表多行记录合并为一行后,与主表做一连接)
--将上述SQL语句与主表做一个连接查询select bd.DETAIL_ID,TITLE,bcm.COMMENT_INFOfrom BBSDETAIL bd,(select DETAIL_ID,wmsys.wm_concat('{'||COMMENT_BODY||'}') as COMMENT_INFO from BBSCOMMENT group by DETAIL_ID) bcmwhere 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 {卖家还是挺热心的,以后再来},{东西不错}
- Oracle之多行记录变一行记录,行变列,并排序(wmsys.wm_concat)
- oracle聚合函数wmsys.wm_concat超长问题记录
- oracle数据库合并行记录,WMSYS.WM_CONCAT函数的用法
- oracle wmsys.wm_concat() 多行传为一行
- Oracle数据库合并行记录,WMSYS.WM_CONCAT 函數的用法
- Oracle数据库合并行记录,【WMSYS.WM_CONCAT】 函數的用法 ----【行列转换】
- Oracle数据库合并行记录,WMSYS.WM_CONCAT 函數的用法
- Oracle数据库合并行记录,WMSYS.WM_CONCAT 函數的用法
- Oracle数据库合并行记录,WMSYS.WM_CONCAT 函數的用法
- WMSYS.WM_CONCAT排序
- oracle wmsys.wm_concat函数
- oracle wmsys.wm_concat用法
- oracle之wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- Oracle中的wmsys.wm_concat
- SQL+合并多个记录group_concat()、wmsys.wm_concat()
- 好朋友,格力造
- Protocol Buffer技术详解(数据编码)
- 解决myeclispe syso alt+/自动提示
- C语言内存分配问题和C语言中的内存
- 各种错误提示的意义及原因
- Oracle之多行记录变一行记录,行变列,并排序(wmsys.wm_concat)
- cocos2dx引擎基本结构
- oracle--sql内部处理机制
- 行转列方法,decode()与wmsys.wm_concat()
- PAT1002
- getDimension,getDimensionPixelOffset和getDimensionPixelSize
- org.json.JSONException: Value of type java.lang.String cannot be converted to JSONArra
- protobuf在netty里面的应用举例
- 多线程-使用队列统计目录下的文件的内容行数