mysql排序

由于mysql没有row_number()方法,只能通过其它方法来进行排序,以下为使用变量来实现排序

示例1:以col1分组,col2正序排序

SET @i=0;
SET @col1='';
SELECT col1
     , col2 
     , @i:=IF(@col1=col1, @i+1, 1) AS seq
     , @col1:=col1
FROM (
  SELECT 'a' AS col1, 1 AS col2
  UNION ALL
  SELECT 'b' AS col1, 2 AS col2
  UNION ALL
  SELECT 'c' AS col1, 3 AS col2
  UNION ALL
  SELECT 'a' AS col1, 4 AS col2
)a
ORDER BY col1, col2
;
输出结果:
col1      col2     seq  @col1:=col1  
------  ------  ------  -------------
a            1       1  a            
a            4       2  a            
b            2       1  b            
c            3       1  c                   

示例2:以col1分组,col2倒序排序

SET @i=0;
SET @col1='';
SELECT col1
     , col2 
     , @i:=IF(@col1=col1, @i+1, 1) AS seq
     , @col1:=col1
FROM (
  SELECT 'a' AS col1, 1 AS col2
  UNION ALL
  SELECT 'b' AS col1, 2 AS col2
  UNION ALL
  SELECT 'c' AS col1, 3 AS col2
  UNION ALL
  SELECT 'a' AS col1, 4 AS col2
)a
ORDER BY col1, col2 DESC
;
输出结果:
col1      col2     seq  @col1:=col1  
------  ------  ------  -------------
a            4       1  a            
a            1       2  a            
b            2       1  b            
c            3       1  c