mysql - 使用连接表 Mysql 按同一列排序

Question

我有两张桌子：母亲和孩子。每个母亲都有一个或多个孩子。

母亲：

id | name  | age 
1  | name1 | 30
2  | name2 | 40 
3  | name3 | 35 

孩子：

id | name   | age | id_mother
1  | child1 | 15  | 1 
2  | child2 | 10  | 2

我想要的结果是按年龄 ASC 为母亲订购，并按他们的孩子年龄为 ASC 订购

编辑（来自评论）：我只想按最小孩子年龄（如果孩子存在），然后按母亲年龄。

例如 ：

id | name  | age 
2  | name2 | 40 
1  | name1 | 30 
3  | name3 | 35

我试过这个sql：

SELECT * 
FROM mother m
ORDER BY m.age asc , (SELECT MIN(age) FROM child c WHERE c.id_mother == m.id)

但它只给按年龄排序的母亲？

标签： mysql

Answer 1

try using this method :
select * from mother;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | name1 |  30 |
|  2 | name2 |  40 |
|  3 | name3 |  35 |
+----+-------+-----+

select * from child;
+----+--------+-----+-----------+
| id | name   | age | id_mother |
+----+--------+-----+-----------+
|  1 | child1 |  15 |         1 |
|  2 | child2 |  10 |         2 |
+----+--------+-----+-----------+


SELECT mo1. * , (SELECT MIN( ch1.age ) FROM child AS ch1 WHERE ch1.id_mother = mo1.id) AS child_age FROM mother AS mo1 ORDER BY child_age IS NOT NULL DESC , child_age ASC ;
+----+-------+-----+-----------+
| id | name  | age | child_age |
+----+-------+-----+-----------+
|  2 | name2 |  40 |        10 |
|  1 | name1 |  30 |        15 |
|  3 | name3 |  35 |      NULL |
+----+-------+-----+-----------+