manytomy关系连接三个表，结果限制在mysql的第三个表上

9cbw7uwe 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(363)

我有三张table有很多种关系。1.播放列表2.曲目3.播放列表\曲目
我想检索10个播放列表，最多5首歌曲。我该怎么做？我试过用这种方法。。

select t1.id as playlistId, t2.playlist_id as pivot_playlist_id, t2.track_id as track_id 
from playlist t1 
join playlist_tracks t2 on t1.id = t2.playlist_id
left join (
    select t3.id as track_id , t3.is_published 
    from tracks t3
    where t3.is_published = 1 
    group by t3.id limit 5
    ) xx on t2.track_id = xx.track_id 
where t1.prior != 0 AND t1.is_published = 1 ;

但并没有得到正确的结果，因为这是不适用于轨道限制。它返回特定播放列表中的所有歌曲。哪里做错了>请帮帮我。这是同一个sqlfiddle.com/#的dbfiddle！1年2月9日

mysql php laravel

来源：https://stackoverflow.com/questions/53845912/manytomany-relationship-join-three-tables-with-limit-on-result-of-third-table-li

1条答案

按热度按时间

ymdaylpp1#

尝试此操作，每个播放列表\u曲目限制5首歌曲：

SET @playlist_id=-1;
SELECT * FROM
(
  SELECT track_id,
@row_number:=CASE
    WHEN @playlist_id= playlist_id THEN @row_number + 1
    ELSE 1
END AS track_number,
@playlist_id:=playlist_id as playlist_id
FROM playlist_tracks
ORDER BY playlist_id
) X WHERE track_number<=5

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

我来回答

manytomy关系连接三个表，结果限制在mysql的第三个表上

1条答案

相关问题

热门标签

最新问答