mysql中需要类似于oralce中rownum, row_number()的场景分析和SQL实现

1.单纯需要知道记录所在的行号,或排名.假设表tbl按col1排序:

SELECT * 
FROM (SELECT @rownum:=0) c,
(SELECT a.*,@rownum:=IFNULL(@rownum,0)+1 rownum  FROM tbl a ORDER BY a.col1) b

2.如果表tbl中,按count字段排序,并有一个字段rank专用于存储记录的排名,在每一次排序字段更新后需要知道最新的排名.如投票,可以对数据进行重新排序.这种场景一般是指表的数据量不多,但经常需要重新排序的情况.大表频率操作更新不适用(可以考虑添加where条件,限定记录重排.如投票场景,1个人原本是10票,后来是15票,那么可以限定9票到16票的人进行重排,而不是整体重排).

UPDATE ${tableName} a,(SELECT @rownum:=0) c,
(SELECT a.*,@rownum:=IFNULL(@rownum,0)+1 rownum FROM ${tableName} a ORDER BY a.count) b
SET a.rank=b.rownum
WHERE a.id=b.id

3.分组排序.假设一个表tbl需要按col1分组,按col2排序.

SELECT *
FROM (SELECT @rownum:=0,@d1:='',@d2:='') a,
(SELECT col1, col2, @d1:=@d2 d1, @d2:=col1 d2, @rownum:=IF(@d1=@d2,@rownum,0)+1 rownum
FROM tbl
ORDER BY col1,col2) b

这里并没有使用GROUP BY 关键字,但如果需要的,可以在此基础上修改,嵌套多一层即可.

 

以上的SQL关键都是一个单独的查询SELECT @rownum:=0, 可以使SQL在运行过程中使用一个变量.同理也可以定义多个变量.并且可以将变量之间的操作和判断作一些简单的组合(值传递,值的重新计算,值的判断),因为SQL自带循环结构,所以利用好这种结构,则可以避免写一些后台代码,也不必使用存储过程.

和分组排序,类似的功能,如各种报表中需要用到从2005到2018年,从1月到6月等等限定有规律的值的例,也如自动排班,

ID	工作时间
1	星期一
2	星期二
3	星期三
4	星期四
5	星期五
6	星期六
7	星期日
8	星期一