解决方案 »
- 一个查询语句的优化
- Centos无图形化界面如何安装oracle10g
- 关于读取orcl里日志记录问题,请高手帮忙????????
- 对SDO_GEOMETRY类型数据的读取和导入
- dbsnmp是什么意思?
- 求助:服务启动失败,ISQL*PLUS怎样以SYS进行连接到指定的数据库?
- Oracle关于tunning的一个发现~ 顺便散分
- 请教:对于ORACLE数据库,SQL语句中的注视是否能起到优化性能的作用?
- 想问问改变ORACLE里最大进程的数据由原来的100改为200,对数据库会有什么影响???
- 想在hp-unix11上安装oracle,应选哪个版本?
- 在索引列上可以建触发器吗?
- 急,关于ORACLE的问题
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 原来也是窗口函数啊 ~