这是我的表设置:

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)

这是我想做的:
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)

但是,我想使用类似“SELECT * FROM ORDER BY(SELECT a FROM b ORDER BY z)”的语法。

这可能吗?

最佳答案

只要子查询返回的结果恰好为1,就可以按子查询进行排序,这意味着您需要在子查询中联接表。

我只是在我的应用程序中的查询生成器中设置了它,类似这样的东西应该对您有用:

SELECT a.*
FROM a,b
WHERE a.id = b.a
ORDER BY (
   SELECT z
   FROM b
   WHERE a.id = b.a
) ASC

关于mysql按选择顺序-可能吗?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/813495/

10-13 06:53