为分组查询添加一列按分组字段从1开始计数的序号列

*************************************************************************************************
// date   2008-1-24
// auth   mircle

//  qq     83529101

//msn     [e-mail]
// class  oracle
**************************************************************************************************
（部门--人员 一对多 一个部门可以有多个员工，同一个部门内员工不重名）
employee 表

部门	人员
A	tom
A	john
A	mary
B	longman
B	solong
C	tomy
C	lincon

----------------------------------------------------------------------- -----------------------
增加一列序号 ：要求 序号要按部门不同 从1开始计数
要得到的结果如下
-------------------------------------------------------------------------------------------------

部门	人员	序号
A	john	1
A	mary	2
A	tom	3
B	longman	1
B	solong	2
C	lincon	1
C	tomy	2

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

 oracle sql：

select 部门，人员，(select sum(1) from employee emA
                             where  emA.部门=emB.部门
                              and    emA.人员<=emB.人员) as 序号
from employee emB
order by emB.部门，emB.人员 
 

这个sql智慧的地方是在：

 

计算小于它的有多少条记录来计算它的序号
---------------------------------------------------------------------------------------------------

 

评论

6 楼 mircle 2008-09-12  

当然   上面的sql还可以演变成如下

create  table  stu (id  varchar(2),
                     name  varchar(5));


insert  into stu('1' ,'hello');
insert  into stu('3','john');
insert  into stu('4',tom);


select  *  from  stu  如下

ID NAME
1 hello
3 john
4 tom

因为使用自增长ID ， 所以，有时删除后 就会出现断号的问题
那我们怎么才能得到连续的号码呢   通过把上面的sql演变可以得到答案

select  id,name,(select  sum(1)  from  stu  emA  
                 where  ema.id<=emB.Id
                 )  as  序号

from stu emB
order by emB.id,emB.name


ID NAME 序号
1 hello 1
3 john 2
4 tom 3


希望能够对 到我博客的人有所帮助：）

5 楼 让胡路车站 2008-03-10  

1.rank() over()
  序号会出现跳号。如1，2，2，4，5
2.dense_rank() over()
  序号不会出现跳号。如1，2，2，3，4
3.row_number() over()
  不会出现相同顺序号。如1，2，3，4，5

4 楼 mircle 2008-02-18  

引用

yerba 20 天前
使用现有的oracle函数才体现你的智慧，sorry，请原谅我说的话。你那句sql你在大数据量上执行一下就知道了，很快就会游标超出最大值。
告诉你最简单的方式：
select T.*, rank()over(partition by DEPT order by NAME desc)
from employee t

没关系的 ！ 我写出来只是想提供一个我知道的方法，你告诉了我一个更好的方法
我还不知道怎么感谢你呢  ：）  

3 楼 mircle 2008-02-18  

引用

yerba 20 天前
使用现有的oracle函数才体现你的智慧，sorry，请原谅我说的话。你那句sql你在大数据量上执行一下就知道了，很快就会游标超出最大值。
告诉你最简单的方式：
select T.*, rank()over(partition by DEPT order by NAME desc)
from employee t

没关系的 ！ 我写出来只是想提供一个我知道的方法，你告诉了我一个更好的方法
我还不知道怎么感谢你呢  ：）  

2 楼 yerba 2008-01-29  

使用现有的oracle函数才体现你的智慧，sorry，请原谅我说的话。你那句sql你在大数据量上执行一下就知道了，很快就会游标超出最大值。
告诉你最简单的方式：
select T.*, rank()over(partition by DEPT order by NAME desc)
from employee t

1 楼 LucasLee 2008-01-24  

巧了，我正好刚用了类似的方式计算序号。
不过我用的不是sum(1),我用的是count(*)，会不会更直观点？