呵呵,出来了 select aa.a,b,rownum-(select count(*) c from aa where a<bb.a) from aa , (select a from aa group by a)bb where aa.a=bb.a
815的写法: select table_name, column_name, seq from (select table_name, column_name, (select count(table_name) from user_tab_columns t2 where t2.table_name = t1.table_name and t2.column_name <= t1.column_name) seq from user_tab_columns t1 order by table_name) order by table_name, column_name, seq
select 字段1, 字段2, rank() over(partition by 字段1 order by 字段1,字段2) 序号 from 表名
可是,我用了,好像提示over关键词不存在
15:39:12 SQL> select * from tb;COL1 COL2 ---------- ---------- A aaa A aab A aac B bba B bbb B bbc B bbd C ccc已选择8行。已用时间: 00: 00: 00.47 15:39:25 SQL> select t2.col1,t2.col2,t2.id-t1.mid+1 from ( 15:39:31 2 select min(id) mid,col1 from ( 15:39:31 3 select rownum id,col1,col2 from ( 15:39:31 4 select * from tb order by col1,col2) 15:39:31 5 ) t 15:39:31 6 group by col1) t1, 15:39:31 7 (select rownum id,col1,col2 from 15:39:31 8 (select * from tb order by col1,col2) 15:39:31 9 ) t2 15:39:33 10 where t1.col1=t2.col1;COL1 COL2 T2.ID-T1.MID+1 ---------- ---------- -------------- A aaa 1 A aab 2 A aac 3 B bba 1 B bbb 2 B bbc 3 B bbd 4 C ccc 1已选择8行。已用时间: 00: 00: 00.47 15:39:38 SQL>
--测试: create table tb_1(col1 varchar2(10),col2 number); insert into tb_1 values('A', 11); insert into tb_1 values('C', 31); insert into tb_1 values('A', 14); insert into tb_1 values('B', 21); insert into tb_1 values('A', 32); insert into tb_1 values('B', 42); insert into tb_1 values('C', 34);select a.*,(select sum(1) from tb_1 where col1 = a.col1 and col2 <=a.col2) as seq from tb_1 a order by col1,seq ;/* COL1 COL2 SEQ ---------- ---------- ---------- A 11 1 A 14 2 A 32 3 B 21 1 B 42 2 C 31 1 C 34 2已选择7行。*/
select a.*, (select sum(1) from tb_1 where col1 = a.col1 and col2 <=a.col2) as seq from tb_1 a order by col1,seq ;
select aa.a,b,rownum-(select count(*) c from aa where a<bb.a) from aa
,
(select a from aa group by a)bb
where aa.a=bb.a
select table_name, column_name, seq
from (select table_name,
column_name,
(select count(table_name)
from user_tab_columns t2
where t2.table_name = t1.table_name and
t2.column_name <= t1.column_name) seq
from user_tab_columns t1
order by table_name)
order by table_name, column_name, seq
字段1,
字段2,
rank() over(partition by 字段1 order by 字段1,字段2) 序号
from
表名
---------- ----------
A aaa
A aab
A aac
B bba
B bbb
B bbc
B bbd
C ccc已选择8行。已用时间: 00: 00: 00.47
15:39:25 SQL> select t2.col1,t2.col2,t2.id-t1.mid+1 from (
15:39:31 2 select min(id) mid,col1 from (
15:39:31 3 select rownum id,col1,col2 from (
15:39:31 4 select * from tb order by col1,col2)
15:39:31 5 ) t
15:39:31 6 group by col1) t1,
15:39:31 7 (select rownum id,col1,col2 from
15:39:31 8 (select * from tb order by col1,col2)
15:39:31 9 ) t2
15:39:33 10 where t1.col1=t2.col1;COL1 COL2 T2.ID-T1.MID+1
---------- ---------- --------------
A aaa 1
A aab 2
A aac 3
B bba 1
B bbb 2
B bbc 3
B bbd 4
C ccc 1已选择8行。已用时间: 00: 00: 00.47
15:39:38 SQL>
create table tb_1(col1 varchar2(10),col2 number);
insert into tb_1 values('A', 11);
insert into tb_1 values('C', 31);
insert into tb_1 values('A', 14);
insert into tb_1 values('B', 21);
insert into tb_1 values('A', 32);
insert into tb_1 values('B', 42);
insert into tb_1 values('C', 34);select a.*,(select sum(1) from tb_1 where col1 = a.col1 and col2 <=a.col2) as seq from tb_1 a
order by col1,seq ;/*
COL1 COL2 SEQ
---------- ---------- ----------
A 11 1
A 14 2
A 32 3
B 21 1
B 42 2
C 31 1
C 34 2已选择7行。*/
(select sum(1) from tb_1 where col1 = a.col1 and col2 <=a.col2) as seq
from tb_1 a
order by col1,seq ;