PHP MySQL连接两个表

Question

我有两张表，歌曲和历史。歌曲表如下所示:

ID | title      | artist      | duration
1  | some title | some artist | 83592

历史表如下所示:

ID | title      |   artist    | duration | date_played
5  | some title | some artist | 83592    | 2012-08-08 11:22:00

如果历史表中最新条目中的标题和艺术家匹配，我将如何回显歌曲表中的 ID？

我尝试了 SELECT * FROM history JOIN songs ON title=songs.title AND artist=songs.artist ORDER BY date_played DESC LIMIT 0, 1 但这没有用。有什么想法吗？

Answer 1

SELECT s.ID
FROM songs s
INNER JOIN (SELECT * FROM history h ORDER BY date_played DESC LIMIT 1) lastHistory
ON lastHistory.title = s.title AND lastHistory.artist = s.artist

( Sqlfiddle )