mysql - 我如何在MySQL的子查询中按MAX(日期)进行ORDER？

我在这个问题上遇到了麻烦：
我有两张桌子：样品和歌曲
我想选择

pathName   from samples
id_user    from samples
id         from songs
name       from songs

每个示例都属于一首歌：我想将共享同一个歌曲ID的每个示例分组，以便选择最后一个输入日期。
所以我把这两张桌子连接起来：

$query = 'SELECT
            samples.pathName path_name,
            samples.id_user id_user,
            songs.id id_song,
            songs.name song_name
        FROM (SELECT *, MAX(date) AS maxDate FROM samples GROUP BY id_song ORDER BY maxDate) samples
        INNER JOIN songs songs
        ON songs.id = samples.id_chanson
        WHERE songs.finished = false';

它不应该那么重，我希望我清楚…：）

最佳答案

尝试：

SELECT sa.pathName,
       sa.id_user,
       so.id id_song,
       so.name song_name
FROM songs so
JOIN (SELECT id_song, MAX(`date`) AS maxDate FROM samples GROUP BY id_song) mx
  ON so.id = mx.id_song
JOIN samples sa
  on mx.id_song = sa.id_song and mx.maxDate = sa.`date`
WHERE so.finished = 'false'
ORDER BY mx.maxDate

关于mysql - 我如何在MySQL的子查询中按MAX(日期)进行ORDER？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16997734/