select m.parent_id,
m.start_date,
wmsys.wm_concat( m.cleck_name) As name,
wmsys.wm_concat( m.userId) As userId,
max(rowid) as rid
from (select t.parent_id,
to_char(t.start_date, 'yyyy-MM-dd') start_date,
s.cleck_name,
s.user_orderby,
t.sche_type,
s.id as userId
from oa_assistant_schedulerplan t, s_user s
where t.user_id = s.id(+)
and t.sche_type != '1'
and t.parent_id is not null
order by s.user_orderby) m
item
01
AA
01
BB
02
CC
02
DD
02
EE
03
FF
04
GG
04
HH
希望将no相同的列整合为一条记录如下
no
items
01
AA,BB
02
CC,DD,EE
03
FF
04
GG,HH
MYSQL中,直接有group_contact函数了,如下:
select id,group_contact(items)
from TABLE group by id
而oracle中,对应的有wm_contact,摘抄例子如下:
select id,wmsys.wm_concat(items) name from table
group by id;
m.start_date,
wmsys.wm_concat( m.cleck_name) As name,
wmsys.wm_concat( m.userId) As userId,
max(rowid) as rid
from (select t.parent_id,
to_char(t.start_date, 'yyyy-MM-dd') start_date,
s.cleck_name,
s.user_orderby,
t.sche_type,
s.id as userId
from oa_assistant_schedulerplan t, s_user s
where t.user_id = s.id(+)
and t.sche_type != '1'
and t.parent_id is not null
order by s.user_orderby) m
group by m.parent_id, m.start_date
按parent_id和start_date分组后合并每个组的cleck_name和userId
另一个实例:
来自http://blog.sina.com.cn/s/blog_56d8ea900100zlwv.html
数据库的结构如下:
no
01
01
02
02
02
03
04
04
希望将no相同的列整合为一条记录如下
no
01
02
03
04
MYSQL中,直接有group_contact函数了,如下:
而oracle中,对应的有wm_contact,摘抄例子如下:
select id,wmsys.wm_concat(items) name from table
group by id;