背景
样例测试环境
在日常工作中,有不同的排序需求
正文样例测试环境
- 服务器版本
服务器版本
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)
- 客户端版本
客户端版本
Ver 14.12 Distrib 5.0.41, for Win32(ia32)
- 表结构
表结构tb1
CREATE TABLE `tb1` ( `id` int(10) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- 数据
表数据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');
- 随机排序
如下示例可看到,执行两次的结果是不一样的随机排序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)
- 根据自定义的顺序排序
如,我希望根据 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)
- 上面的数据可以看到,实现了我们的想法,不过是不是太过笨拙了呢?下面有一个神器出场
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)
- 根据权重排序
比如如我们有需要根据一个名字的相似度进行排序,如果包含三个字符串的,则优先显示,包含两个的其次,只包含一个的最后显示,如下为一个示例,并且将排序的条件输出以便做分析:权重排序示例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 进行排序,这个就经常被用到。
- 产生顺序编号
这个与排序只是有点擦边,顺带附上:生成顺序编号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 的子句则会对每一行执行一遍
- 网络