这是我的表设置:
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/