Oracle记录顺序号,求一句Sql，按照日期自动生成顺序号(Oralce实现)

[php]

SQL>  drop table saleTable;

表已丢弃。

SQL>  create table saleTable(

2   ID varchar2(20),     SALEDATE date);

表已创建。

SQL> begin

2  insert into saletable values('TS00000001',to_date('20061230233927','yyyymmddhh24miss'));

3  insert into saletable values('TS00000058',to_date('20070102004515  ','yyyymmddhh24miss'));

4  insert into saletable values('TS00000070',to_date('20070106230104 ','yyyymmddhh24miss'));

5  insert into saletable values('TS00000071',to_date('20070106230108 ','yyyymmddhh24miss'));

6  insert into saletable values('TS00000073',to_date('20070106231659 ','yyyymmddhh24miss'));

7  insert into saletable values('TS00000109',to_date('20070111224452  ','yyyymmddhh24miss'));

8  insert into saletable values('TS00000110',to_date('20070111225055  ','yyyymmddhh24miss'));

9  insert into saletable values('TS00000111',to_date('20070111225821  ','yyyymmddhh24miss'));

10  insert into saletable values('TS00000112',to_date('20070111235041  ','yyyymmddhh24miss'));

11  end;

12  /

PL/SQL 过程已成功完成。

SQL> select id,saledate,row_number() over(partition by to_char(saledate,'yyyymmdd')

2   order by saledate) rn from saletable;

ID                   SALEDATE               RN

-------------------- -------------- ----------

TS00000001           20061230233927          1

TS00000058           20070102004515          1

TS00000070           20070106230104          1

TS00000071           20070106230108          2

TS00000073           20070106231659          3

TS00000109           20070111224452          1

TS00000110           20070111225055          2

TS00000111           20070111225821          3

TS00000112           20070111235041          4

已选择9行。

SQL>

SQL>

[/php]

[本帖最后由 nokilled 于 2008-1-7 20:36 编辑]