按与子条目相关的顺序对mysql select进行排序

l3zydbqr 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(392)

我有一个包含父项和子项的数据库表。前任。

+---+-------+-------+---------+
|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    |
+---+-------+-------+---------+

写这个剧本最好的方法是什么？
仅供参考。对不起，我的英语不好。如果有人能把这个写得更好，欢迎你。谢谢

mysql

来源：https://stackoverflow.com/questions/53374790/sort-mysql-select-in-the-order-of-related-to-the-child-entry

2条答案

按热度按时间

yacmzcpb1#

试着用下面的方法

SELECT T1.*
FROM TABLE T1
INNER JOIN
  (SELECT ID,parent
   FROM TABLE
   WHERE parent=0) T2 ON T1.parent = T2.ID
ORDER BY T1.added DESC

//Output

id  title   parent  added   
6   title6  2       10:15   
5   title5  1       10:10   
4   title4  3       10:05

赞(0）回复(0）举报 2021-06-19

yqkkidmi2#

如果一个父母只能有一个孩子，你可以用一个简单的 LEFT JOIN 然后由孩子来排序结果 added 价值：

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演示
如果一个家长可能有多个孩子，您需要找到 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

备用演示

赞(0）回复(0）举报 2021-06-19

我来回答

按与子条目相关的顺序对mysql select进行排序

2条答案

相关问题

热门标签

最新问答