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 11g 配置监听
- pl/sql中如何全部依次执行if elsif分支?求高人指教
- oracel怎么样连接access,是sql语句写的?
- 优化一个SQL语句
- 第一次运行SQLPLUS时,提示ORACLE initialization or shutdown in progress 是什么意思,应该怎么办??在线等
- oracle的D:\oracle\ora81\sqlplus\demo目录下的例子执行为什么会出错,怎么解决
- 请教一个数据库表设计、查询的问题:关于树状结构的表设计『急,在线等』
- 表的索引问题?
- 存储过程中的输入参数指定了默认值,使用DBMS_SCHEDULER调用时不能直接使用默认值???
- 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个字段来显示的.所以说还是达不到楼主的要求,建议用过程做一下.