select c1,c2,row_number() over (partition by c1 order by c1) seq
from t1
order by c1,c2
/
from t1
order by c1,c2
/
解决方案 »
- oracle10g只有部分dbf文件,如何恢复数据?
- select * from dept 当前 deptno 的 dname 值取 deptno+1 的 dname,如果是最大的,取最小的那个
- 用JDBC提交的查询为什么不返回
- 急!!!Tools表空间20天增加了2G,如何解决其增长过快的问题?
- oracle 查询的问题
- database link SYSTEM.LOCALDOMAIN does not exist
- 请问用exp可以按条件来备份oracle9i吗?
- 上海建坤信息技术有限责任公司招聘软件工程师
- 关于对动态SQL求SUM值的问题(oracle8i)?
- 如何用查询出来后的结果作为where条件
- 请教:关于创建表空间时数据文件的放置目录
- ORA-00600 什么错误啊?
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 ;