我有两个桌子。我想在users_logs.music_id=users_likes.music_id和users_logs.user_id=users_likes.user_id的地方加入他们
该表名为users_logs，并具有重复行。
+----+----------+---------+
| id | music_id | user_id |
+----+----------+---------+
| id | 1 | 1 |
+----+----------+---------+
| id | 2 | 1 |
+----+----------+---------+
| id | 1 | 1 |
+----+----------+---------+
| id | 3 | 2 |
+----+----------+---------+
| id | 3 | 1 |
+----+----------+---------+
该表名为users_likes。
+----+----------+---------+
| id | music_id | user_id |
+----+----------+---------+
| id | 1 | 1 |
+----+----------+---------+
| id | 2 | 2 |
+----+----------+---------+
| id | 5 | 1 |
+----+----------+---------+
从
user_id=1的user_logs获得降序后最后3行后，我想在下面输出：1. music_id = 3 like = 0
2. music_id = 1 like = 1
3. music_id = 2 like = 0
如何在mysql中仅执行1个查询呢？
最佳答案
如果我正确地遵循了逻辑，则此查询应该执行您想要的操作。假定id列指定从user_logs获取最后三行的顺序：
select ul.music_id, (uli.user_id is not null) as `like`
from (select ul.*
from user_logs ul
where user_id = 1
order by id desc
limit 3
) ul left outer join
users_likes uli
on ul.user_id = uli.user_id and ul.music_id = uli.music_id;