mysql 通过选择排序——可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/813495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql order by select -- possible?
提问by Alex Schmid
Here is my table setup:
这是我的表设置：
mysql> describe a;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| x | int(11) | YES | | NULL | |
| y | varchar(1) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> describe b;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| a | int(11) | NO | | NULL | |
| z | varchar(1) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from a;
+----+------+------+
| id | x | y |
+----+------+------+
| 1 | 1 | a |
| 2 | 2 | b |
| 3 | 3 | c |
| 4 | 4 | d |
+----+------+------+
4 rows in set (0.01 sec)
mysql> select * from b;
+----+---+------+
| id | a | z |
+----+---+------+
| 1 | 3 | q |
| 2 | 2 | a |
| 3 | 1 | u |
| 4 | 4 | x |
+----+---+------+
4 rows in set (0.01 sec)
Here's what I want to do:
这是我想要做的：
mysql> select a.* from a, b where a.id = b.a order by b.z;
+----+------+------+
| id | x | y |
+----+------+------+
| 2 | 2 | b |
| 3 | 3 | c |
| 1 | 1 | a |
| 4 | 4 | d |
+----+------+------+
4 rows in set (0.00 sec)
However, I want to use a syntax like "SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)".
但是，我想使用像“SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)”这样的语法。
Is this possible?
这可能吗？
回答by Alex Schmid
Sorting by a Subquery is possible as long as the Subquery returns exactly 1 result, what means you need to join the tables within the subquery.
只要子查询只返回 1 个结果，就可以按子查询排序，这意味着您需要连接子查询中的表。
I just setup this in a query-builder within my application, something like this should work for you:
我只是在我的应用程序中的查询构建器中设置它，这样的东西应该适合你：
SELECT a.*
FROM a,b
WHERE a.id = b.a
ORDER BY (
SELECT z
FROM b
WHERE a.id = b.a
) ASC
回答by marcgg
I really don't think that it's possible the way you describe it, I think that you will need to to join the tables.
我真的不认为你描述的方式是可能的，我认为你需要加入表格。
Look this up : http://www.w3schools.com/Sql/sql_join_inner.asp
回答by Bill Karwin
SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)
No, this will not get you what you want. In the first place, the subquery returns more than one row, so it can't be used as an expressions in the ORDER BYof the outer query. This is the error you get:
不，这不会让你得到你想要的。首先，子查询返回多行，因此不能用作ORDER BY外部查询中的表达式。这是你得到的错误：
ERROR 1242 (21000): Subquery returns more than 1 row
Also, there's no correlation between the rows of aand the order of the rows in the subquery, given that form of query. In other words, even if you've sorted the values in the subquery, that doesn't make the rows of the outer query sort in a matching order.
此外，a考虑到这种形式的查询，子查询中的行和行的顺序之间没有相关性。换句话说，即使您对子查询中的值进行了排序，也不会使外部查询的行按匹配顺序排序。
If you're trying to ensure only a single row in the result set per row if a(even when there are multiple matching rows in b), then you probably want this:
如果您试图确保每行结果集中只有一行a（即使 中有多个匹配的行b），那么您可能需要这样：
SELECT DISTINCT a.*
FROM a JOIN b ON (a.id = b.a)
ORDER BY b.z;
