数据库*有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);
5 个解决方案
#1
select PRMID, CREATTIME, EMPID,
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;
#2
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确实很麻烦，就想到这么多了，可能我想复杂了……
#3
不懂，来看看
#4
mark!!!!
#5
csdn出问题了，我登陆了依旧看不了得分恢复啊
#1
select PRMID, CREATTIME, EMPID,
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;
#2
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确实很麻烦，就想到这么多了，可能我想复杂了……
#3
不懂，来看看
#4
mark!!!!
#5
csdn出问题了，我登陆了依旧看不了得分恢复啊