学步园

在日常工作中，有不同的排序需求

正文
样例测试环境

• 服务器版本
  服务器版本

  Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

• 客户端版本
  客户端版本

  Ver 14.12 Distrib 5.0.41, for Win32(ia32)

准备测试数据

1. 表结构
   表结构tb1

   CREATE TABLE `tb1` (
   `id` int(10) NOT NULL default '0',
   `name` char(20) default NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
                   

2. 数据
   表数据tb1

   INSERT INTO `tb1` VALUES ('1', 'aaaaa');
   INSERT INTO `tb1` VALUES ('2', 'aaabb');
   INSERT INTO `tb1` VALUES ('3', 'aabbc');
   INSERT INTO `tb1` VALUES ('4', 'bbbbb');
   INSERT INTO `tb1` VALUES ('5', 'ddddd');
   		        

测试样例

1. 随机排序
   如下示例可看到，执行两次的结果是不一样的
   随机排序

   mysql> select id
       -> from tb1
       -> order by rand();
   +----+
   | id |
   +----+
   |  5 |
   |  1 |
   |  2 |
   |  4 |
   |  3 |
   +----+
   5 rows in set (0.00 sec)
   
   mysql> select id
       -> from tb1
       -> order by rand();
   +----+
   | id |
   +----+
   |  3 |
   |  5 |
   |  1 |
   |  2 |
   |  4 |
   +----+
   5 rows in set (0.00 sec)
   			    

2. 根据自定义的顺序排序
   如，我希望根据 1 3 5 2 4 的顺序排列。很容易想到的一个办法是 union all：
   union all 方式的自定义排序

   mysql> select id from tb1 where id = 1
       -> union all
       -> select id from tb1 where id = 3
       -> union all
       -> select id from tb1 where id = 5
       -> union all
       -> select id from tb1 where id = 2
       -> union all
       -> select id from tb1 where id = 4;
   +----+
   | id |
   +----+
   |  1 |
   |  3 |
   |  5 |
   |  2 |
   |  4 |
   +----+
   5 rows in set (0.03 sec)
   
   				

3. 上面的数据可以看到，实现了我们的想法，不过是不是太过笨拙了呢？下面有一个神器出场
   find_in_set 函数辅助自定义排序

   mysql> select id
       -> from tb1
       -> order by find_in_set(id, "1,3,5,2,4");
   +----+
   | id |
   +----+
   |  1 |
   |  3 |
   |  5 |
   |  2 |
   |  4 |
   +----+
   5 rows in set (0.00 sec)
   				

   上例可见实现了排序效果，并且操作时间比前例来得短。顺便说明，find_in_set 也可以做为 where in 方式的查询条件，如：

   find_in_set 函数用作过滤条件

   mysql> select id
       -> from tb1
       -> where find_in_set(id, "1,5,3");
   +----+
   | id |
   +----+
   |  1 |
   |  3 |
   |  5 |
   +----+
   3 rows in set (0.00 sec)
   				

4. 根据权重排序
   比如如我们有需要根据一个名字的相似度进行排序，如果包含三个字符串的，则优先显示，包含两个的其次，只包含一个的最后显示，如下为一个示例，并且将排序的条件输出以便做分析：
   权重排序示例

   mysql> select *,  concat((name like "%a%"), (name like "%b%"), (name like "%c%")
   ) as '权重'
       -> from tb1
       -> where name like "%a%" or name like "%b%"  or name like "%c%"
       -> order by concat((name like "%a%"), (name like "%b%"), (name like "%c%"))
   desc;
   +----+-------+------+
   | id | name  | 权重 |
   +----+-------+------+
   |  3 | aabbc | 111  |
   |  2 | aaabb | 110  |
   |  1 | aaaaa | 100  |
   |  4 | bbbbb | 010  |
   +----+-------+------+
   4 rows in set (0.00 sec)
   				

   这只是一个例子，按日期、数字大小、字典顺序等均是权重的概念，只是没有用到复杂的函数来计算而已。权重的计算根据不同的需求有不同的计算方式，如根据字段的长度，则使用 length 进行排序，这个就经常被用到。

5. 产生顺序编号
   这个与排序只是有点擦边，顺带附上：
   生成顺序编号

   mysql> set @seq := 0;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> select @seq := @seq + 1 as '编号', tb1.*
       -> from tb1;
   +------+----+-------+
   | 编号 | id | name  |
   +------+----+-------+
   |    1 |  1 | aaaaa |
   |    2 |  2 | aaabb |
   |    3 |  3 | aabbc |
   |    4 |  4 | bbbbb |
   |    5 |  5 | ddddd |
   +------+----+-------+
   5 rows in set (0.00 sec)
   				

   这里做一点改动，以将上面的两个语句放置在一个语句里：

   一个语句生成顺序编号

   mysql> select @seq := @seq + 1 as '编号', tb1.*
       -> from tb1, (select @seq := 0) as temp;
   +------+----+-------+
   | 编号 | id | name  |
   +------+----+-------+
   |    1 |  1 | aaaaa |
   |    2 |  2 | aaabb |
   |    3 |  3 | aabbc |
   |    4 |  4 | bbbbb |
   |    5 |  5 | ddddd |
   +------+----+-------+
   5 rows in set (0.00 sec)
   				

   从这个例子还可以看到 SELECT 语句的运行情况：from 子句里的语句只在最初执行一遍，select 的子句则会对每一行执行一遍

参考文档

• 网络