mysql - 对 4 个表进行 JOIN 和 GROUP BY 的复杂 SQL 查询

Question

这是我的表结构(简化):

“视频”表包含所有视频的列表。

video
+----+-------+-------+
| id | title | views |
+----+-------+-------+

“ channel ”表包含所有可能的 channel 。使用“video_channel”表与“video”建立多对多关系。

channel
+----+-------+
| id | title |
+----+-------+

video_channel
+----+----------+------------+
| id | video_id | channel_id |
+----+----------+------------+

“拇指”表包含每个视频的多个拇指:

thumb
+----+------+----------+
| id | link | video_id |
+----+------+----------+

我需要得到的是:

+---------------+-----------------+-------------------------------------------+
| channel.title | number of video | first thumb of most viewed video for this | 
|               | per channel     | channel                                   |
+---------------+-----------------+-------------------------------------------+

我设法得到了这个:

+---------------+-----------------+
| channel.title | number of video |
+---------------+-----------------+

使用此查询:

SELECT channel.title, COUNT(*) 
FROM video 
INNER JOIN video_channel ON video_channel.video_id=video.id
INNER JOIN channel ON video_channel.channel_id=channel.id
GROUP BY video_channel.channel_id
ORDER BY COUNT(*) DESC 

我使用MySql

Answer 1

似乎您需要一个相关子查询。

假设 SQL Server，这是我原来的方言，你可以这样做:

select
    channel.title,
    count(video_channel.video_id),
    _mostViewedThumb.link
from
    video_channel -- count
    inner join channel on -- title
        video_channel.channel_id = channel.id
    cross apply ( -- most viewed
        select top 1
            thumb.link
        from
            thumb
            inner join video on -- for order
                thumb.video_id = video.id
        where
            video_channel.video_id = thumb.video_id
        order by
            video.views desc
    ) as _mostViewedThumb
group by
    channel.title;