with tb as ( select '王' name,'数学' course, 11 type, '2011-11-30 18:11:00' time from dual union all select '王' , '数学', 11, '2011-11-30 18:11:00' from dual union all select '王' , '语文', 12, '2011-10-30 18:11:00' from dual union all select '张' , '数学', 11, '2011-11-30 18:11:00' from dual ) select tt.name, wm_concat(tt.km), wm_concat(tt.sj) from ( select name, to_char(course), to_char(t.a) as km, to_char(type) || '/' || to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'), 'mm.dd') as sj from (select name, course, count(1) a, time, type from tb group by name,course,time,type) t ) tt group by tt.name
select regexp_substr('1001,1002,1003,1004,1005', '[^,]+', 1, level) from dual connect by level <= 5
select '王' name,'数学' course, 11 type, '2011-11-30 18:11:00' time from dual union all
select '王' , '数学', 11, '2011-11-30 18:11:00' from dual union all
select '王' , '语文', 12, '2011-10-30 18:11:00' from dual union all
select '张' , '数学', 11, '2011-11-30 18:11:00' from dual
) select tt.name, wm_concat(tt.km), wm_concat(tt.sj)
from (
select name,
to_char(course),
to_char(t.a) as km,
to_char(type) || '/' || to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'), 'mm.dd') as sj
from (select name, course, count(1) a, time, type
from tb
group by name,course,time,type) t
) tt
group by tt.name
connect by level <= 5
ORACLE中的支持正则表达式的函数主要有下面四个:
1,REGEXP_LIKE :与LIKE的功能相似
2,REGEXP_INSTR :与INSTR的功能相似
3,REGEXP_SUBSTR :与SUBSTR的功能相似
4,REGEXP_REPLACE :与REPLACE的功能相似