select 字段1,max(sys_connect_by_path(字段2||字段3,' ')) result
from (select 字段1,字段2,字段3,
(row_number() over(order by 字段1,字段2,字段3desc)
+ dense_rank() over(order by 字段1)) rn,
max(字段2) over(partition by 字段1) qs
from table)
start with id = qs
connect by rn-1 = prior rn
group by 字段1
from (select 字段1,字段2,字段3,
(row_number() over(order by 字段1,字段2,字段3desc)
+ dense_rank() over(order by 字段1)) rn,
max(字段2) over(partition by 字段1) qs
from table)
start with id = qs
connect by rn-1 = prior rn
group by 字段1
解决方案 »
- 组合索引和单个索引对数据库性能的影响
- 请问用< 或 > 号会用到索引吗?
- 一台oracle服务器是否能同时启动多个监听,监听不同端口?
- 优化ORACLE库数据查询问题
- 方案与表
- 装了ORACLE8I客户端后~如果连接多个ORACLE服务器。
- ORACLE中PL-SQL写程序可以允许递归吗?
- 急!求助大家一个SQL的问题!
- 为什么OracleOraHome90ManagementServer服务起不来
- 请教一个语句
- Archive process error: ORACLE Instance oemrep - Can not allocate log, archival required
- oracle9i中,怎样使一个列为自动增长型的?能说的详细点吗?
from (
select col1,col2,col3,
(row_number() over(order by col1,col2,col3 desc) + dense_rank() over(order by col1)) rn,
(max(col2) over(partition by col1)) qs
from test
)
start with id = qs
connect by rn-1 = prior rn
group by col1
/
SQL> /
start with id = qs
*
ERROR 位于第 8 行:
ORA-00904: "ID": 无效的标识符
请问这个id是从哪来的啊?另外能解释一下其中使用道德函数吗?谢谢
Connected as SYSSQL>
SQL> create table test(col1 varchar2(10),col2 number,col3 number);Table createdSQL> insert into test select 'a',1,11 from dual;1 row insertedSQL> insert into test select 'a',2,22 from dual;1 row insertedSQL> insert into test select 'a',3,13 from dual;1 row insertedSQL> insert into test select 'b',1,1 from dual;1 row insertedSQL> insert into test select 'b',2,3 from dual;1 row insertedSQL> commit;Commit completeSQL> select * from test;COL1 COL2 COL3
---------- ---------- ----------
a 1 11
a 2 22
a 3 13
b 1 1
b 2 3SQL>
SQL>
SQL> select col1,sum(decode(col2,1,col2)) a,sum(decode(col2,1,col3)) b,
2 sum(decode(col2,2,col2)) c,sum(decode(col2,2,col3)) d,
3 sum(decode(col2,3,col2)) e,sum(decode(col2,3,col3)) f
4 from test group by col1;COL1 A B C D E F
---------- ---------- ---------- ---------- ---------- ---------- ----------
a 1 11 2 22 3 13
b 1 1 2 3 SQL>
ORA-00904: "ID": 无效的标识符
--------------------------------------是9i的!
建议楼主买本书学一下,我一下也不好说清楚的.就是有关层次查询!
把ID改为COL2.应该可以显示出一点效果,但它是用2个字段来显示的.所以说还是达不到楼主的要求,建议用过程做一下.