mysql多条件排序查询

概述

数据库中的数据直接呈现出来一般不是我们想要的，所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤，

我们可能还需要对数据进行排序，比如想从列表中了解消费最高的项，就可能需要对金额字段做降序排序，想看年龄从小到大的分布情况，就可能需要对user表的age字段进行升序排序。

也可能需要对数据进行限制，比如我们需要对付款的1~10，11~20，21~30 名的用户分别赠予不同的礼品，这时候对数据的限制就很有用了。

备注：下面脚本中[]包含的表示可选，| 分隔符表示可选其一。

数据排序 order by

语法格式如下：

1、需要排序的字段跟在order by之后；

2、asc 和 desc表示排序的规则，asc：升序，desc：降序，默认为升序 asc；

3、排序可以指定多次字段，多字段排序之间用逗号隔开。

4、多字段排序中，越靠前优先级越高，下面中cname1优先排序，当cname1等值的时候，cname2开始排序，直至所有字段都排序完。

ｓｅｌｅｃｔ cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;单个字段排序

举个例子，在销售额中通按照交易的订单进行金额额度降序的方式显示：

1 mysql> ｓｅｌｅｃｔ * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount| goods | 4 +---------+---------+---------+-------+ 5 |8 | brand| 52.2| 2 | 6 |9 | hen | 1752.02 | 7 | 7 |10 | helyn| 88.5| 4 | 8 |11 | sol | 1007.9|11 | 9 |12 | diny| 12| 1 |10 |13 | weng| 52.2| 5 |11 |14 | sally| 99.71| 9 |12 +---------+---------+---------+-------+13 7 rows in set14 15 mysql> ｓｅｌｅｃｔ * from t_order order by amount desc;16 +---------+---------+---------+-------+17 | orderid | account | amount| goods |18 +---------+---------+---------+-------+19 |9 | hen | 1752.02 | 7 |20 |11 | sol | 1007.9|11 |21 |14 | sally| 99.71| 9 |22 |10 | helyn| 88.5| 4 |23 |8 | brand| 52.2| 2 |24 |13 | weng| 52.2| 5 |25 |12 | diny| 12| 1 |26 +---------+---------+---------+-------+27 7 rows in set多个字段排序

多个字段排序用逗号隔开，优先级从左到右逐次递减，如下图，如果金额一致，则按照购买商品数量从多到少排序：

mysql> ｓｅｌｅｃｔ * from t_order order by amount desc,goods desc; +---------+---------+---------+-------+ | orderid | account | amount| goods | +---------+---------+---------+-------+ |9 | hen | 1752.02 | 7 | |11 | sol | 1007.9|11 | |14 | sally| 99.71| 9 | |10 | helyn| 88.5| 4 | |13 | weng| 52.2| 5 | |8 | brand| 52.2| 2 | |12 | diny| 12| 1 | +---------+---------+---------+-------+ 7 rows in set

按alias排序

按照别名排序或者做条件查询的目的都是为了简化代码，方便使用，别名可以是英文，也可以是中文：

mysql> ｓｅｌｅｃｔ account as ac,amount as am,goods as gd from t_order order by am,gd desc; +-------+---------+----+ | ac| am| gd | +-------+---------+----+ | diny| 12|1 | | weng| 52.2|5 | | brand | 52.2|2 | | helyn | 88.5|4 | | sally | 99.71|9 | | sol| 1007.9| 11 | | hen| 1752.02 |7 | +-------+---------+----+ 7 rows in set字段排序中使用函数

下面使用了abs取绝对值函数，所以在 am字段降序排序中，-99.99 排在 99.71之上。

1 mysql> ｓｅｌｅｃｔ * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount| goods | 4 +---------+---------+---------+-------+ 5 |8 | brand| 52.2| 2 | 6 |9 | hen | 1752.02 | 7 | 7 |10 | helyn| 88.5| 4 | 8 |11 | sol | 1007.9|11 | 9 |12 | diny| 12| 1 |10 |13 | weng| 52.2| 5 |11 |14 | sally| 99.71| 9 |12 |15 | brand1| -99.99| 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> ｓｅｌｅｃｔ account as ac,amount as am,goods as gd from t_order order by abs(am) desc;1718 +--------+---------+----+19 | ac | am| gd |20 +--------+---------+----+21 | hen| 1752.02 |7 |22 | sol| 1007.9| 11 |23 | brand1 | -99.99|5 |24 | sally| 99.71|9 |25 | helyn| 88.5|4 |26 | brand| 52.2|2 |27 | weng| 52.2|5 |28 | diny| 12|1 |29 +--------+---------+----+30 8 rows in set与Where条件结合使用

order 在 where 条件之后，根据where已经过滤好的数据再进行排序。下面是过滤出购买金额>80 且 购买数量>5的数据，并且按照价格降序排序。

1 mysql> ｓｅｌｅｃｔ * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount| goods | 4 +---------+---------+---------+-------+ 5 |8 | brand| 52.2| 2 | 6 |9 | hen | 1752.02 | 7 | 7 |10 | helyn| 88.5| 4 | 8 |11 | sol | 1007.9|11 | 9 |12 | diny| 12| 1 |10 |13 | weng| 52.2| 5 |11 |14 | sally| 99.71| 9 |12 |15 | brand1| -99.99| 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> ｓｅｌｅｃｔ * from t_order where amount>80 and goods>5 order by amount desc;17 +---------+---------+---------+-------+18 | orderid | account | amount| goods |19 +---------+---------+---------+-------+20 |9 | hen | 1752.02 | 7 |21 |11 | sol | 1007.9|11 |22 |14 | sally| 99.71| 9 |23 +---------+---------+---------+-------+数据limit

很多时候我们过滤出符合要求的数据之后，还需要得到这些数据中的某一个具体区间，比如对付款超过1000的用户的第1~10，11~20，21~30 名分别赠予不同的礼品，这时候就要使用limit操作了。

limit用来限制ｓｅｌｅｃｔ查询返回的数据，常用于数据排行或者分页等情况。

语法格式如下：

ｓｅｌｅｃｔ cname from tname limit [offset,] count;

1、offset表示偏移量，就是指跳过的行数，可以省略不写，默认为0，表示跳过0行，如 limit 8 等同于 limit 0,8。

2、count：跳过偏移量offset之后开始取的数据行数，有count行。

3、limit中offset和count的值不能用表达式。

获取前n条记录

如下图，limit n 和 limit 0,n 是一致的：

1 mysql> ｓｅｌｅｃｔ * from t_order; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount| goods | 4 +---------+---------+---------+-------+ 5 |8 | brand| 52.2| 2 | 6 |9 | hen | 1752.02 | 7 | 7 |10 | helyn| 88.5| 4 | 8 |11 | sol | 1007.9|11 | 9 |12 | diny| 12| 1 |10 |13 | weng| 52.2| 5 |11 |14 | sally| 99.71| 9 |12 |15 | brand1| -99.99| 5 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> ｓｅｌｅｃｔ * from t_order limit 217 ;18 +---------+---------+---------+-------+19 | orderid | account | amount| goods |20 +---------+---------+---------+-------+21 |8 | brand| 52.2| 2 |22 |9 | hen | 1752.02 | 7 |23 +---------+---------+---------+-------+24 2 rows in set25 26 mysql> ｓｅｌｅｃｔ * from t_order limit 0,2;27 +---------+---------+---------+-------+28 | orderid | account | amount| goods |29 +---------+---------+---------+-------+30 |8 | brand| 52.2| 2 |31 |9 | hen | 1752.02 | 7 |32 +---------+---------+---------+-------+33 2 rows in set limit限制单条记录

这边我们获取支付金额中最大和最小的的一条记录。可以先使用 order 条件进行排序，然后limit 第1条记录即可：

mysql> ｓｅｌｅｃｔ * from t_order;+---------+---------+---------+-------+| orderid | account | amount| goods |+---------+---------+---------+-------+|8 | brand| 52.2| 2 ||9 | hen | 1752.02 | 7 ||10 | helyn| 88.5| 4 ||11 | sol | 1007.9|11 ||12 | diny| 12| 1 ||13 | weng| 52.2| 5 ||14 | sally| 99.71| 9 ||15 | brand1| -99.99| 5 |+---------+---------+---------+-------+8 rows in set mysql> ｓｅｌｅｃｔ * from t_order where amount>0 order by amount desc limit 1;+---------+---------+---------+-------+| orderid | account | amount| goods |+---------+---------+---------+-------+|9 | hen | 1752.02 | 7 |+---------+---------+---------+-------+1 row in set mysql> ｓｅｌｅｃｔ * from t_order where amount>0 order by amount asc limit 1;+---------+---------+--------+-------+| orderid | account | amount | goods |+---------+---------+--------+-------+|12 | diny| 12 | 1 |+---------+---------+--------+-------+1 row in set获取(m,n)区间记录

即跳过m条，获取n条，示例如下，我们跳过两条，从第三条开始，连取四条的操作：

1 mysql> ｓｅｌｅｃｔ * from t_order order by amount; 2 +---------+---------+---------+-------+ 3 | orderid | account | amount| goods | 4 +---------+---------+---------+-------+ 5 |15 | brand1| -99.99| 5 | 6 |12 | diny| 12| 1 | 7 |8 | brand| 52.2| 2 | 8 |13 | weng| 52.2| 5 | 9 |10 | helyn| 88.5| 4 |10 |14 | sally| 99.71| 9 |11 |11 | sol | 1007.9|11 |12 |9 | hen | 1752.02 | 7 |13 +---------+---------+---------+-------+14 8 rows in set15 16 mysql> ｓｅｌｅｃｔ * from t_order order by amount limit 2,4;17 +---------+---------+--------+-------+18 | orderid | account | amount | goods |19 +---------+---------+--------+-------+20 |8 | brand| 52.2| 2 |21 |13 | weng| 52.2| 5 |22 |10 | helyn| 88.5| 4 |23 |14 | sally| 99.71| 9 |24 +---------+---------+--------+-------+25 4 rows in set

分页的做法与这个类似，我们程序业务上看到的分页一般有 pageIndex，pageSize等参数，我们通常的做法是 limit pageIndex*pageSize,pageSize。

这边假设有31条数据，每页数量pageSize=10，页面索引pageIndex默认0，则第一页就是 limit 0,10，第二页 limit10,10，第三页 limit 20,10，第四页 limit 30,10。

注意点：

1、limit 后面不能使用表达式，只能使用明确的数值，否则会爆出异常，比如 limit 0*10,10，是不对的，这个上面提过了。

2、limit后续的数值只能是正整数和0，也就是说，不能是负数，否则同样会报错。

3、排序字段的值相同情况下，排序后分页会出现混乱重复的情况。

第3点详细说明下：假如根据age排序，但是有多个age都是20岁的同学，这时候我们3条记录一页，就会出现分页混乱数据重复。因为年龄相同的人有多个，这是几个人的排序在每次查询的时候会有不确定性。

举个例子，下面的分页，混乱了

1 mysql> ｓｅｌｅｃｔ * from user3; 2 +----+------+-------+ 3 | id | age| name| 4 +----+------+-------+ 5 |1 |20 | brand | 6 |2 |22 | sol| 7 |3 |20 | helen | 8 |4 | 19.5 | diny| 9 |6 |19 | a |10 |7 |20 | b |11 |8 |20 | c |12 |9 |20 | d |13 | 10 |20 | e |14 | 11 |23 | f |15 +----+------+-------+16 10 rows in set17 18 mysql> ｓｅｌｅｃｔ * from user3 order by age limit 0,3; 19 +----+------+-------+20 | id | age| name|21 +----+------+-------+22 |6 |19 | a |23 |4 | 19.5 | diny|24 |3 |20 | helen |25 +----+------+-------+26 3 rows in set27 28 mysql> ｓｅｌｅｃｔ * from user3 order by age limit 3,3;2930 +----+-----+-------+31 | id | age | name|32 +----+-----+-------+33 |3 |20 | helen |34 |7 |20 | b |35 |8 |20 | c |36 +----+-----+-------+37 3 rows in set38 39 mysql> ｓｅｌｅｃｔ * from user3 order by age limit 6,3;4041 +----+-----+-------+42 | id | age | name|43 +----+-----+-------+44 |7 |20 | b |45 |3 |20 | helen |46 |2 |22 | sol|47 +----+-----+-------+48 3 rows in set

我们的做法是使用重复值字段做排序的时候再加个唯一依据（一般可以设主键），就不会混乱了。

如下示例，正常了：

1 mysql> ｓｅｌｅｃｔ * from user3; 2 +----+------+-------+ 3 | id | age| name| 4 +----+------+-------+ 5 |1 |20 | brand | 6 |2 |22 | sol| 7 |3 |20 | helen | 8 |4 | 19.5 | diny| 9 |6 |19 | a |10 |7 |20 | b |11 |8 |20 | c |12 |9 |20 | d |13 | 10 |20 | e |14 | 11 |23 | f |15 +----+------+-------+16 10 rows in set17 18 mysql> ｓｅｌｅｃｔ * from user3 order by age,id limit 0,3;1920 +----+------+-------+21 | id | age| name|22 +----+------+-------+23 |6 |19 | a |24 |4 | 19.5 | diny|25 |1 |20 | brand |26 +----+------+-------+27 3 rows in set28 29 mysql> ｓｅｌｅｃｔ * from user3 order by age,id limit 3,3;3031 +----+-----+-------+32 | id | age | name|33 +----+-----+-------+34 |3 |20 | helen |35 |7 |20 | b |36 |8 |20 | c |37 +----+-----+-------+38 3 rows in set39 40 mysql> ｓｅｌｅｃｔ * from user3 order by age,id limit 6,3;4142 +----+-----+------+43 | id | age | name |44 +----+-----+------+45 |9 |20 | d|46 | 10 |20 | e|47 |2 |22 | sol|48 +----+-----+------+49 3 rows in set上述总结

1、order by cname [asc|desc] 用于对查询结果排序，asc为升序，desc为降序，可以省略，省略情况下默认为asc。

2、limit用来限制查询结果返回的行数，包含2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取的行数。limit中offset可以省略，默认值为0；limit中offset 和 count都必须大于等于0；limit中offset和count的值不能用表达式。

3、分页排序时，排序字段不要有重复值，重复值情况下可能会导致分页结果混乱，建议在后面加一个主键排序。

为帮助开发者们提升面试技能、有机会入职BATJ等大厂公司，特别制作了这个专辑——这一次整体放出。

大致内容包括了： Java 集合、JVM、多线程、并发编程、设计模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大厂面试题等、等技术栈！

欢迎大家关注公众号【Java烂猪皮】，回复【666】，获取以上最新Java后端架构VIP学习资料以及视频学习教程，然后一起学习，一文在手，面试我有。

每一个专栏都是大家非常关心，和非常有价值的话题，如果我的文章对你有所帮助，还请帮忙点赞、好评、转发一下，你的支持会激励我输出更高质量的文章，非常感谢！