select num,name,replace(wm_concat('【'|| ||'】'),',','') from _table group by num,name
--9i可以使用如下语句 with t as( select 1 id, 2 num,'小明' name,'起床' workspace from dual union all select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all select 4 id, 5 num,'波波' name,'工作' workspace from dual union all select 5 id, 5 num,'波波' name,'很忙' workspace from dual) --以上是样例数据 SELECT a.num, a.name, ltrim(MAX(sys_connect_by_path('【'||workspace||'】', ' ')), ' ') workspace FROM (SELECT row_number() over(PARTITION BY t.num, t.name ORDER BY id) rn, t.* FROM t) a START WITH rn = 1 CONNECT BY PRIOR rn = rn - 1 AND a.num = PRIOR a.num AND a.name = PRIOR a.name GROUP BY a.num, a.name;
with tab as( select 1 id, 2 num,'小明' name,'起床' workspace from dual union all select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all select 4 id, 5 num,'波波' name,'工作' workspace from dual union all select 5 id, 5 num,'波波' name,'很忙' workspace from dual) --以上是样例数据 SELECT num,NAME,Max(REPLACE(workspace,',',' ')) workspace FROM( SELECT NUM,NAME,wm_concat('【'||workspace||'】')over(PARTITION BY num,NAME ORDER BY id ) workspace FROM tab ) GROUP BY num,NAME NUM NAME WORKSPACE ----------------------------------------------- 2 小明 【起床】 【吃饭】 【睡觉】 5 波波 【工作】 【很忙】
能否告诉一下:with tab as()什么意思及其中内容又是什么意思?
大哥,你好厉害,能帮我解释解释max,replace(,,)中参数,wm_concat,over吗?SELECT num,NAME,Max(REPLACE(workspace,',',' ')) workspace FROM( SELECT NUM,NAME,wm_concat('【'||workspace||'】')over(PARTITION BY num,NAME ORDER BY id ) workspace FROM tab
SQL> with tb as( 2 select 1 id, 2 num,'小明' name,'起床' workspace from dual union all 3 select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all 4 select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all 5 select 4 id, 5 num,'波波' name,'工作' workspace from dual union all 6 select 5 id, 5 num,'波波' name,'很忙' workspace from dual) 7 select num,name,max(workspace) workspace 8 from (select num,name,wm_concat(workspace) over(partition by num,name order by id) workspace from tb) 9 group by num,name 10 /
NUM NAME WORKSPACE ---------- ---- -------------------------------------------------------------------------------- 2 小明 起床,吃饭,睡觉 5 波波 工作,很忙
SQL> with tb as( 2 select 1 id, 2 num,'小明' name,'起床' workspace from dual union all 3 select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all 4 select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all 5 select 4 id, 5 num,'波波' name,'工作' workspace from dual union all 6 select 5 id, 5 num,'波波' name,'很忙' workspace from dual) 7 select num,name,max(replace(workspace,',',' ')) workspace 8 from (select num,name,wm_concat('【'||workspace||'】') over(partition by num,name order by id) workspace from tb) 9 group by num,name 10 /
NUM NAME WORKSPACE ---------- ---- -------------------------------------------------------------------------------- 2 小明 【起床】 【吃饭】 【睡觉】 5 波波 【工作】 【很忙】
from _table
group by num,name
with t as(
select 1 id, 2 num,'小明' name,'起床' workspace from dual union all
select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all
select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all
select 4 id, 5 num,'波波' name,'工作' workspace from dual union all
select 5 id, 5 num,'波波' name,'很忙' workspace from dual)
--以上是样例数据
SELECT a.num,
a.name,
ltrim(MAX(sys_connect_by_path('【'||workspace||'】', ' ')), ' ') workspace
FROM (SELECT row_number() over(PARTITION BY t.num, t.name ORDER BY id) rn,
t.*
FROM t) a
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND a.num = PRIOR a.num
AND a.name = PRIOR a.name
GROUP BY a.num, a.name;
with tab as(
select 1 id, 2 num,'小明' name,'起床' workspace from dual union all
select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all
select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all
select 4 id, 5 num,'波波' name,'工作' workspace from dual union all
select 5 id, 5 num,'波波' name,'很忙' workspace from dual)
--以上是样例数据
SELECT num,NAME,Max(REPLACE(workspace,',',' ')) workspace FROM(
SELECT NUM,NAME,wm_concat('【'||workspace||'】')over(PARTITION BY num,NAME ORDER BY id ) workspace
FROM tab
)
GROUP BY num,NAME
NUM NAME WORKSPACE
-----------------------------------------------
2 小明 【起床】 【吃饭】 【睡觉】
5 波波 【工作】 【很忙】
SELECT NUM,NAME,wm_concat('【'||workspace||'】')over(PARTITION BY num,NAME ORDER BY id ) workspace
FROM tab
2 select 1 id, 2 num,'小明' name,'起床' workspace from dual union all
3 select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all
4 select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all
5 select 4 id, 5 num,'波波' name,'工作' workspace from dual union all
6 select 5 id, 5 num,'波波' name,'很忙' workspace from dual)
7 select num,name,max(workspace) workspace
8 from (select num,name,wm_concat(workspace) over(partition by num,name order by id) workspace from tb)
9 group by num,name
10 /
NUM NAME WORKSPACE
---------- ---- --------------------------------------------------------------------------------
2 小明 起床,吃饭,睡觉
5 波波 工作,很忙
SQL> with tb as(
2 select 1 id, 2 num,'小明' name,'起床' workspace from dual union all
3 select 2 id, 2 num,'小明' name,'吃饭' workspace from dual union all
4 select 3 id, 2 num,'小明' name,'睡觉' workspace from dual union all
5 select 4 id, 5 num,'波波' name,'工作' workspace from dual union all
6 select 5 id, 5 num,'波波' name,'很忙' workspace from dual)
7 select num,name,max(replace(workspace,',',' ')) workspace
8 from (select num,name,wm_concat('【'||workspace||'】') over(partition by num,name order by id) workspace from tb)
9 group by num,name
10 /
NUM NAME WORKSPACE
---------- ---- --------------------------------------------------------------------------------
2 小明 【起床】 【吃饭】 【睡觉】
5 波波 【工作】 【很忙】
wm_concat 原来也是窗口函数啊 ~