表T_MESSAGE中的数据如下:数据库中共有18条记录,我需要取这些记录中PRMID,CREATTIME,EMPID唯一的记录,需要将后面SELECTCOMPNAME,STARTCOMPNAME,HTID,XTMONEY,BUSDESCRIBE这些字段也取唯一值(这个地方需要处理一下,如果有值和空的两种情况,取有值的,如果都为空,就取空值)。最后得到的效果如下图所示:
我已经自己拼了个视图,实现了上述功能,代码如下:
create or replace view v_xt_message_group as
select distinct prmid,empid,
substr(replace(wm_concat(selectcompname),',','~'),instr(replace(wm_concat(selectcompname),',','~'),'~',-1)+1) selectcompname,
substr(replace(wm_concat(startcompname),',','~'),instr(replace(wm_concat(startcompname),',','~'),'~',-1)+1) startcompname,
substr(replace(wm_concat(htid),',','~'),instr(replace(wm_concat(htid),',','~'),'~',-1)+1) htid,
substr(replace(wm_concat(xtmoney),',','~'),instr(replace(wm_concat(xtmoney),',','~'),'~',-1)+1) xtmoney,
substr(replace(wm_concat(busDescribe),',','~'),instr(replace(wm_concat(busDescribe),',','~'),'~',-1)+1) busDescribe
from t_message group by prmid,empid主要利用的是wm_concat函数,但这个函数是10g才有的,我们的正式服务器是9i的,没办法使用,只能想别的办法了。
请教各位高人,在不使用wm_concat函数的情况下,能否有别的方法实现上述效果,包括自己写函数,或是拼视图均可,谢谢了!
下面是数据库表和数据:
create table T_MESSAGE
(
PRMID VARCHAR2(30),
CREATTIME VARCHAR2(19),
EMPID VARCHAR2(30),
SELECTCOMPNAME VARCHAR2(2000),
STARTCOMPNAME VARCHAR2(2000),
HTID VARCHAR2(2000),
XTMONEY VARCHAR2(2000),
BUSDESCRIBE VARCHAR2(2000)
);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', '东北电网有限公司沈阳超高压局', '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', '辽宁省电力有限公司沈阳供电公司', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', null, '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', null, '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
我已经自己拼了个视图,实现了上述功能,代码如下:
create or replace view v_xt_message_group as
select distinct prmid,empid,
substr(replace(wm_concat(selectcompname),',','~'),instr(replace(wm_concat(selectcompname),',','~'),'~',-1)+1) selectcompname,
substr(replace(wm_concat(startcompname),',','~'),instr(replace(wm_concat(startcompname),',','~'),'~',-1)+1) startcompname,
substr(replace(wm_concat(htid),',','~'),instr(replace(wm_concat(htid),',','~'),'~',-1)+1) htid,
substr(replace(wm_concat(xtmoney),',','~'),instr(replace(wm_concat(xtmoney),',','~'),'~',-1)+1) xtmoney,
substr(replace(wm_concat(busDescribe),',','~'),instr(replace(wm_concat(busDescribe),',','~'),'~',-1)+1) busDescribe
from t_message group by prmid,empid主要利用的是wm_concat函数,但这个函数是10g才有的,我们的正式服务器是9i的,没办法使用,只能想别的办法了。
请教各位高人,在不使用wm_concat函数的情况下,能否有别的方法实现上述效果,包括自己写函数,或是拼视图均可,谢谢了!
下面是数据库表和数据:
create table T_MESSAGE
(
PRMID VARCHAR2(30),
CREATTIME VARCHAR2(19),
EMPID VARCHAR2(30),
SELECTCOMPNAME VARCHAR2(2000),
STARTCOMPNAME VARCHAR2(2000),
HTID VARCHAR2(2000),
XTMONEY VARCHAR2(2000),
BUSDESCRIBE VARCHAR2(2000)
);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', '2010-06-17 09:42:17', '910010000187', '东北电网有限公司沈阳超高压局', '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-13 11:28:23', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000752', '2010-06-12 18:58:53', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', '辽宁省电力有限公司沈阳供电公司', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901110000722', '2010-06-11 16:43:54', '910010000187', null, '辽宁省电力有限公司本部', null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', null, '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
insert into T_MESSAGE (PRMID, CREATTIME, EMPID, SELECTCOMPNAME, STARTCOMPNAME, HTID, XTMONEY, BUSDESCRIBE)
values ('70901210000792', null, '910010000187', '东北电网有限公司沈阳超高压局', null, null, null, null);
解决方案 »
- oracle 语句优化。有关联条件,但表在连接时还是merge join,建成临时表或变换表位置变成hash join 是什么原因
- -------->Oracle11g安装问题<----------
- Oracle 10g 字段限制修改
- 系统突然瘫痪,如何恢复数据
- 在线等待,求 一 简单 Sql 语句......
- 为什么说SQL命令未正确结束?
- asp.net中连接oracle及select问题
- 初学者的菜问题
- 急急!ORACLE数据库中服务器与客户的问题!
- 如何修改oracle帐户的登陆密码?
- Toad怎么一打开,它就去连接所有数据库呢
- sql*plus插入表数据之后关掉数据库为何没保存插入的内容?
max(SELECTCOMPNAME) as SELECTCOMPNAME,
max(STARTCOMPNAME) as STARTCOMPNAME,
max(HTID) as HTID,
max(XTMONEY) as XTMONEY,
max(BUSDESCRIBE) as BUSDESCRIBE
from T_MESSAGE
group by PRMID, CREATTIME, EMPID;
select distinct a.prmid,a.empid,b.SELECTCOMPNAME,c.STARTCOMPNAME
from (
select distinct a.prmid,a.empid from t_message a
)a
left join
(
select SELECTCOMPNAME,prmid from t_message a where SELECTCOMPNAME is not null
union
select SELECTCOMPNAME,prmid from t_message a where SELECTCOMPNAME is null
and a.prmid not in (
select distinct prmid from t_message a where SELECTCOMPNAME is not null
)
)b on a.prmid = b.prmid
left join
(
select STARTCOMPNAME,prmid from t_message a where a.STARTCOMPNAME is not null
union
select STARTCOMPNAME,prmid from t_message a where a.STARTCOMPNAME is null
and a.prmid not in(
select distinct prmid from t_message a where a.STARTCOMPNAME is not null
)
)c on a.prmid = c.prmid;不用wm_concat确实很麻烦,就想到这么多了,可能我想复杂了……