mysql - 按照与CHILD条目相关的顺序对MYSQL SELECT进行排序

Question

我有包含父条目和子条目的数据库表。例如。

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|1  |title1 |0      |09:50    |
|2  |title2 |0      |09:55    |
|3  |title3 |0      |10:00    |
|4  |title4 |3      |10:05    |
|5  |title5 |1      |10:10    |
|6  |title6 |2      |10:15    |
+---+-------+-------+---------+

我只需要选择 parent = 0 但按照添加到表中的最新子条目的顺序。

SELECT * FROM table WHERE parent=0 ORDER BY added DESC

这只会给我以下输出

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|3  |title3 |0      |10:00    |
|2  |title2 |0      |09:55    |
|1  |title1 |0      |09:50    |
+---+-------+-------+---------+

但由于与父顺序相关的 id 4、5、6 添加为 2、1、3，所以我的期望是

+---+-------+-------+---------+
|id |title  |parent | added   |
+---+-------+-------+---------+
|2  |title2 |0      |09:55    |
|1  |title1 |0      |09:50    |
|3  |title3 |0      |10:00    |
+---+-------+-------+---------+

编写此脚本的最佳方式是什么？

仅供引用。对不起我糟糕的英语。如果有人能以这种更好的方式编写，欢迎您。谢谢

Answer 1

如果 parent 只能有一个 child ，您可以通过对 child 进行简单的 LEFT JOIN 来实现这一点，然后根据 child 添加的 值对结果进行排序:

SELECT t1.*
FROM table1 t1
LEFT JOIN table1 t2 ON t2.parent = t1.id
WHERE t1.parent = 0
ORDER BY t2.added DESC

输出

id  title   parent  added
2   title2  0       09:55
1   title1  0       09:50
3   title3  0       10:00

SQLFiddle demo

如果 parent 可能有多个 child ，您将需要找到每个 parent 的 child 的 MAX(added) 并按其排序:

SELECT t1.*
FROM table1 t1
LEFT JOIN (SELECT parent, MAX(added) AS added 
           FROM table1 t2 
           GROUP BY parent) t2
    ON t2.parent = t1.id
WHERE t1.parent = 0
ORDER BY t2.added DESC

Alternate demo