mysql排序

来源：互联网发布：什么绘画软件好编辑：程序博客网时间：2024/05/16 12:30

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

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

SET @i=0;SET @col1='';SELECT col1     , col2      , @i:=IF(@col1=col1, @i+1, 1) AS seq     , @col1:=col1FROM (  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)aORDER 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:=col1FROM (  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)aORDER 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

0 0