MySQL查询显示行号

使用mysql查询显示行号：

Oracle中有专门的rownum()显示行号的函数，而MySQL没有专门的显示行号函数，但可以通过用@rownum自定义变量显示行号。

主要代码：

(@rownum := @rownum + 1) rownum

(SELECT @rownum := 0) AS rn

例：

SELECT 
  (@rownum := @rownum + 1) AS rownum,
  ci.* 
FROM
  customer_info ci,
  (SELECT 
    @rownum := 0) AS rn 

如果按照某个字段排序，行号会不规则排列，换成先排序，外层加上行号：

SELECT 
  (@rownum := @rownum + 1) AS rownum,
  L.* 
FROM
  (SELECT 
    lci.CONTRACT_NUMBER,
    lci.CONTRACT_STATUS,
    lpi.PROJECT_NO,
    lpi.PROJECT_NAME 
  FROM
    lb_contract_info lci 
    LEFT JOIN lb_project_info lpi 
      ON lpi.ID = lci.PROJECT_ID 
  WHERE lpi.PROJECT_NAME LIKE '%小%' 
  ORDER BY lci.CONTRACT_STATUS DESC) L,
  (SELECT 
    @rownum := 0) AS rn 
ORDER BY rownum

 

转载请注明出处：BestEternity亲笔。