1,row_number - 产生序列号
oracle
SQL> create table test
2 (name varchar2(10),
3 dt date
4 );
Table created.
SQL> insert into test
2 select chr(round(dbms_random.value(97, 122))),created
3 from all_objects where rownum<=100;
100 rows created.
SQL> select test.*
2 ,row_number() over(partition by name order by dt)
3 from test
4 order by name;
NAME DT ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYDT)
---------- --------- ------------------------------------------
a 24-AUG-13 1
b 24-AUG-13 1
b 24-AUG-13 2
b 24-AUG-13 3
b 24-AUG-13 4
c 24-AUG-13 1
c 24-AUG-13 2
c 24-AUG-13 3
其他分析函数
sqlserver类似
....待续