rownum和row_number()

1. rownum
   ROWNUM是Oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。ROWNUM可以用于限制查询返回的总行数，且不能以任何表的名称作为前缀。

   -- ROWNUM对于等于某值的查询（ROWNUM=1有记录，等于其他自然数则无记录）
   SELECT * FROM user t WHERE ROWNUM = 1;
   
   -- ROWNUM对于大于某值的查询（必须使用子查询，且子查询中ROWNUM必须要有别名）
   SELECT * FROM (SELECT ROWNUM rn, id, name FROM user) WHERE rn > 2;
   
   -- ROWNUM对于小于某值的查询
   SELECT * FROM user WHERE ROWNUM < 3;
   
   -- ROWNUM排序（先将结果集加入ROWNUM伪列，然后再排序）
   SELECT ROWNUM, id, name FROM (SELECT * FROM user ORDER BY name DESC);

2. row_number()

   -- row_number()语法：
   row_number() OVER([PARTITION BY col1[,col2, ...]] ORDER BY col1[, col2, ...])
   -- 按照col1[,col2,...]进行分组，再在每个分组中按照col1[,col2,...]进行排序，最后返回排序好的结果集
   
   -- 查询用户表，按照name分组，在每个分组中按照id排序
   SELECT row_number() OVER(PARTITION BY name ORDER BY ID) AS rn, ID, name FROM USER
   
   -- 建立临时表，查询用户id、name，并根据name分组，在各分组中按id排序。
   -- 查询同名的用户记录
   WITH t_tmp AS (
     SELECT row_number() OVER(PARTITION BY name ORDER BY ID) AS rn, ID, name FROM USER
   )
   
   SELECT COUNT(rn), name FROM t_tmp 
   GROUP BY name
   HAVING COUNT(rn) > 1;