《SQL经典实例》：7. 编号，分组

按照某标准排序并添加编号列

按照姓名字母由小到大排序，并赋予编号

SELECT x.ename, x.num
FROM
(SELECT ename,(SELECT count(*) FROM emp b WHERE b.ename <= a.ename) AS num 
FROM emp a)x
ORDER BY ename; 

得到如下结果：

按照工资又高到低排序，并赋予编号，（只展示前五位）

SELECT x.ename, x.sal, x.num 
FROM
(SELECT a.ename, a.sal,
(SELECT count(distinct b.sal) FROM emp b WHERE b.sal >= a.sal) AS num
FROM emp a)x
WHERE num <= 5
ORDER BY x.num;

得到如下结果：