解决方案 »
- PLS-00103: 出现符号 ")"在需要下列之一时: <an identifier>
- liunx下oracle数据库导入导出问题
- 求教Oracle数据库,表的行级触发器(涉及一连串的触发)。现网问题,紧急求助!!!急!!!
- oracle填入中文,就变成问号
- ORA-22835 缓冲区太小
- 新手菜鸟问题:如何把ORACLE数据表导出到WORD或者EXCEL中?在线等..
- 跪求一个检测数据库中的记录是否过期的触发器.
- 大侠,帮帮忙,PROC*C 编译器是不是该设置什么,怎么自带的都是编译出错的。。。
- oracle卡住
- 怎么做到,只在数据库中保存最近一个月的数据,自动覆盖旧数据?
- oralce二个表关联查询的小问题,在线等
- 新手求解oracle配置问题
第二行数据,为什么得到的是CCC.
WITH T AS (
SELECT '001' AS ID, 'XX' AS NAME, 'AAA' AS COM FROM DUAL UNION ALL
SELECT '001' AS ID, 'XX' AS NAME, 'BBB' AS COM FROM DUAL UNION ALL
SELECT '001' AS ID, 'XX' AS NAME, 'CCC' AS COM FROM DUAL UNION ALL
SELECT '002' AS ID, 'YY' AS NAME, 'DDD' AS COM FROM DUAL UNION ALL
SELECT '003' AS ID, 'ZZ' AS NAME, 'EEE' AS COM FROM DUAL UNION ALL
SELECT '003' AS ID, 'ZZ' AS NAME, 'FFF' AS COM FROM DUAL)
SELECT DISTINCT ID, NAME,
(SELECT COM FROM (SELECT ID, NAME,RANK() OVER(PARTITION BY ID ORDER BY COM) RN, COM FROM T) WHERE RN = 1 AND ID = T.ID),
(SELECT COM FROM (SELECT ID, NAME,RANK() OVER(PARTITION BY ID ORDER BY COM) RN, COM FROM T) WHERE RN = 2 AND ID = T.ID)
FROM T;
笨方法
COMMENT 是关键字不能作为字段名称吧
with t as (
select '001' id ,'XX' a,'AAA' b from dual union all
select '001' id ,'XX' a,'BBBB' b from dual union all
select '001' id ,'XX' a,'CCC' b from dual union all
select '002' id , 'YY' a,'CCC' b from dual union all
select '003' id , 'ZZ' a,'EEE' b from dual union all
select '003' id , 'ZZ' a,'FFF' b from dual
),
s as (
select id,a,WMSYS.WM_CONCAT(b)||',' b from t group by id,a
)
select id,a,substr(b,1,instr(b,',',1)-1) COMMENT1 ,
substr(b,instr(b,',',1)+1,instr(b,',',2)-1) COMMENT2
from s
--create table
CREATE GLOBAL TEMPORARY TABLE TEST_TABLE
(
"NO"VARCHAR2(25),
"ID"VARCHAR2(25),
"NAME"VARCHAR2(25),
"COMMENT"VARCHAR2(25)
)on commit delete rows;
--INSERT
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('1','001','XX','AAA');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('2','001','XX','BBBB');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('3','001','XX','CCC');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('4','002','YY','DDD');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('5','003','ZZ','EEE');
INSERT INTO TEST_TABLE("NO","ID","NAME","COMMENT")VALUES('6','003','ZZ','FFF');--select
select t.id,t.name
,decode(
sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-1)
,1
,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<1)
and rownum=1)
)C1
,decode(
sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-2)
,1
,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<2)
and rownum=1)
)C2
/*
IF count(id)> 3
select id 第一个
&& 除去结果的前 3-1 条记录
*/
,decode(
sign((select count(*)from test_table ct1 where t.id=ct1.id)+1-3)
,1
,(select ttl."COMMENT" from TEST_TABLE ttl where ttl."ID"=t."ID"
and ttl."NO" not in(select tt2."NO" from TEST_TABLE tt2 where tt2."ID"=t."ID" and rownum<3)
and rownum=1)
)C3
from test_table t
group by t."ID",t."NAME"
ORDER BY t."ID";
/*
row
1 001 XX AAA BBBB CCC
2 002 YY DDD
3 003 ZZ EEE FFF
*/
因为comment是关键字,所以我将comment修改为了comment1,其他的字段和楼主的一样。sql如下所示:
select d.id,
d.name,
MAX(case
when d.rn = 1 then
d.comment_1
else
null
end) COMMENT1,
MAX(case
when d.rn = 2 then
d.comment_1
else
null
end) COMMENT2
FROM (select t.id,
t.name,
t.comment_1,
row_number() over(partition by t.id, t.name order by t.comment_1) rn
from test_zxc t) d
GROUP BY d.id, d.name
ORDER BY d.id
select id,name,"COMMENT" cc,row_number() over(partition by id order by "COMMENT") rn
from test)
select id,name,wm_concat(decode(rn,1,cc,null)) cc1,wm_concat(decode(rn,2,cc,null)) cc2 from tt where tt.rn<3 group by id,name;