在使用wmsys.wm_concat()处理如下数据时:
ID 内容 序号
------------------------------------------------------
001 1.开始 1
001 2.发展 2
001 3.高潮 3
001 4.结局 4
002 1.开始 1
002 2.发展 2
002 3.高潮 3
002 4.结局 4希望的结果是
ID 内容
-----------------------------------------------------------
001 1.开始,2.发展,3,高潮,4.结局
002 1.开始,2.发展,3,高潮,4.结局
使用的SQL是:
select id,wmsys.wm_concat(内容) from table
group by id
出来的结果是:
ID 内容
-----------------------------------------------------------
001 1.开始,3,高潮,2.发展,4.结局
002 4.结局,1.开始,2.发展,3,高潮
也就是乱序网上查的结果是:10G以后 ORACLE不保证group by后的数据是有序的 期待如何能解决上述问题
ID 内容 序号
------------------------------------------------------
001 1.开始 1
001 2.发展 2
001 3.高潮 3
001 4.结局 4
002 1.开始 1
002 2.发展 2
002 3.高潮 3
002 4.结局 4希望的结果是
ID 内容
-----------------------------------------------------------
001 1.开始,2.发展,3,高潮,4.结局
002 1.开始,2.发展,3,高潮,4.结局
使用的SQL是:
select id,wmsys.wm_concat(内容) from table
group by id
出来的结果是:
ID 内容
-----------------------------------------------------------
001 1.开始,3,高潮,2.发展,4.结局
002 4.结局,1.开始,2.发展,3,高潮
也就是乱序网上查的结果是:10G以后 ORACLE不保证group by后的数据是有序的 期待如何能解决上述问题
WITH tab AS(
SELECT '001' ID,'1.开始' content, 1 seq FROM dual
UNION all
SELECT '001', '2.发展', 2 FROM dual
UNION all
SELECT '001', '3.高潮', 3 FROM dual
UNION all
SELECT '001', '4.结局', 4 FROM dual
UNION ALL
SELECT '001', '1.开始', 1 FROM dual
UNION all
SELECT '002', '2.发展', 2 FROM dual
UNION all
SELECT '002', '3.高潮', 3 FROM dual
UNION all
SELECT '002', '4.结局', 4 FROM dual
)
SELECT id,Max(SubStr(sys_connect_by_path(content,','),2))内容 FROM tab
START WITH seq=1
CONNECT BY PRIOR seq=seq-1
GROUP BY idID 内容
-----------------------------------------------------------
001 1.开始,2.发展,3,高潮,4.结局
002 1.开始,2.发展,3,高潮,4.结局
SQL> WITH tab AS(
2 SELECT '001' ID,'1.开始' content, 1 seq FROM dual
3 UNION all
4 SELECT '001', '2.发展', 2 FROM dual
5 UNION all
6 SELECT '001', '3.高潮', 3 FROM dual
7 UNION all
8 SELECT '001', '4.结局', 4 FROM dual
9 UNION ALL
10 SELECT '002', '1.开始', 1 FROM dual
11 UNION all
12 SELECT '002', '2.发展', 2 FROM dual
13 UNION all
14 SELECT '002', '3.高潮', 3 FROM dual
15 UNION all
16 SELECT '002', '4.结局', 4 FROM dual
17 )
18 select id,content from (
19 SELECT id , wm_concat(content)over(partition by id order by content asc) content,row_number()over(partition by id order by content desc) rn FROM tab)
20 where rn=1
21 ;
ID CONTENT
--- --------------------------------------------------------------------------------
001 1.开始,2.发展,3.高潮,4.结局
002 1.开始,2.发展,3.高潮,4.结局
SQL>
好像有点小问题
数据中有两条“SELECT '001', '1.开始', 1 FROM dual”
结果中“002 1.开始”不应该有CONNECT BY PRIOR seq=seq-1
--加个and
CONNECT BY PRIOR seq=seq-1 and PRIOR ID = ID
SELECT '001' ID,'1.开始' content, 1 seq FROM dual
UNION all
SELECT '001', '2.发展', 2 FROM dual
UNION all
SELECT '001', '3.高潮', 3 FROM dual
UNION all
SELECT '001', '4.结局', 4 FROM dual
UNION ALL
SELECT '002', '1.开始', 1 FROM dual --改成这条!
UNION all
SELECT '002', '2.发展', 2 FROM dual
UNION all
SELECT '002', '3.高潮', 3 FROM dual
UNION all
SELECT '002', '4.结局', 4 FROM dual
)
SELECT id,Max(SubStr(sys_connect_by_path(content,','),2))内容 FROM tab
START WITH seq=1
CONNECT BY PRIOR seq=seq-1
GROUP BY idID 内容
-----------------------------------------------------------
001 1.开始,2.发展,3,高潮,4.结局
002 1.开始,2.发展,3,高潮,4.结局
已写入 file afiedt.buf 1 WITH tb AS(
2 SELECT '001' ID,'1.开始' 内容, 1 序号 FROM dual
3 UNION all
4 SELECT '001', '2.发展', 2 FROM dual
5 UNION all
6 SELECT '001', '3.高潮', 3 FROM dual
7 UNION all
8 SELECT '001', '4.结局', 4 FROM dual
9 UNION ALL
10 SELECT '002', '1.开始', 1 FROM dual
11 UNION all
12 SELECT '002', '2.发展', 2 FROM dual
13 UNION all
14 SELECT '002', '3.高潮', 3 FROM dual
15 UNION all
16 SELECT '002', '4.结局', 4 FROM dual
17 )
18 select id,max(内容) 内容
19 from (select id,wm_concat(内容) over(partition by id order by 序号) 内容
20 from tb)
21* group by id
SQL> /ID 内容
--- --------------------------------------------------
001 1.开始,2.发展,3.高潮,4.结局
002 1.开始,2.发展,3.高潮,4.结局
你在4楼帖的是你执行的源代码和结果么?
4楼那样的数据检索得到的结果就说明那段SQL有问题啊?