mysql - SQL根据子行值对表中的父行进行排序

Question

给出下表:

ID | Name         | Time       | Parent
 1 | Orange       | 1493596800 | 0
 2 | Apple        | 1483228800 | 0
 3 | Red Apple    | 1493596800 | 2
 4 | Yellow Apple | 1493769600 | 2

我想按子行的时间降序对我的表进行排序，并过滤行以使父项必须为零。

例如:

SELECT * 
  FROM MyTable as mt1 
 WHERE Parent = 0 
 Order 
    BY 
     ( SELECT mt2.Time 
         FROM MyTable mt2 
        WHERE mt2.Parent = mt1.ID
     Order BY mt2.Time DESC
        Limit 1
     ) DESC

**输出必须是:**

 2 | Apple        | 1483228800 | 0
 1 | Orange       | 1493596800 | 0

Answer 1

SELECT p.ID, p.Name, MAX(c.Time) AS NewestChildTime, p.Parent
FROM MyTable p
LEFT OUTER JOIN MyTable c
ON c.Parent = p.ID
WHERE p.Parent = 0
GROUP BY p.ID, p.Name, p.Parent
ORDER BY NewestChildTime DESC