订购和限制

Question

在包含城市的表中，我想要获取五个最大的城市并按名称排序：

SELECT * FROM cities ORDER BY population DESC LIMIT 5

这使我获得按人口排序的最大城市，但我想要按名称排序相同的城市。有没有一种简单的方法可以做到这一点（无需转向子查询或随后使用 PHP 对城市进行排序）？

Answer 1

我想你想要的是这样的：

( SELECT * FROM cities ORDER BY population DESC LIMIT 5 ) ORDER BY name;

Answer 2

SELECT * FROM cities ORDER BY population desc, name LIMIT 5

Answer 3

您将需要一个子查询：

SELECT a.* 
FROM (
    SELECT * 
    FROM cities 
    ORDER BY population DESC 
    LIMIT 5
) a 
ORDER BY name;

编辑：刚刚看到您不需要子查询。为什么不呢？这是一个高效的查询，返回速度非常快（应该有一个带有或不带有子查询的人口索引）。

Answer 4

简单地做

SELECT y.*
  FROM (SELECT name FROM cities ORDER BY population DESC LIMIT 5) AS x,
       cities AS y
 WHERE x.name = y.name
 ORDER BY y.name

这就是全部。

干杯。

Answer 5

mysql> create temporary table temp ( ID int );
mysql> insert into temp select ID from cities order by population desc limit 5;
mysql> select a.* from cities a,temp b where a.ID=b.ID order by Name;

连接关闭时会删除临时表，也可以手动删除临时表。从其他连接中无法看到临时表。
正常的方法是（但还不支持）：

mysql> select * from cities where ID in (select ID from cities order by population desc limit 5) order by Name;

但答案是：（

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

尝试过 5.0.5）

Answer 6

SELECT * FROM cities ORDER BY population DESC, name LIMIT 5

你尝试过这个吗？我认为这可行