Mysql数据数据排名

Mysql数据数据排名

根据更新时间进行排名
并列排名
SELECT
*,
(SELECT
COUNT(DISTINCT 排序依据字段)
FROM
t_order AS b
WHERE b.查询条件
AND a.排序依据字段 > b.排序依据字段) + 1 AS rank
FROM
t_order AS a
WHERE a.查询条件
ORDER BY rank;

实例代码

SELECT
  *,
  (SELECT
    COUNT(DISTINCT updatetime)
  FROM
    t_order AS b
  WHERE b.status = "A01"
    AND a.updatetime > b.updatetime) + 1 AS rank
FROM
  t_order AS a
WHERE a.status = "A01"
ORDER BY rank;

非并列排名

SELECT a.*,
(@rowNum:=@rowNum+1) AS rank
FROM t_order AS a,
(SELECT (@rowNum :=0) ) b
WHERE a.查询条件
ORDER BY a.排序依据字段 DESC;

实例代码

SELECT a.*,
(@rowNum:=@rowNum+1) AS rank 
FROM t_order AS a,
(SELECT (@rowNum :=0) ) b
WHERE a.status="A01" 
ORDER BY a.updatetime DESC;