在4个表上使用JOIN和GROUP BY进行复杂的SQL查询

Here is my table structure (simplified) :

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

'Video' table contain a list of all videos.

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

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

'Channel' table contains all possible channels. It's a many to many relation with 'video' using 'video_channel' table.

“频道”表包含所有可能的频道。与使用“video_channel”表的“视频”有很多关系。

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

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

'Thumb' table contains several thumbs for each video :

'Thumb'表格包含每个视频的几个拇指:

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

What i need to get is :

我需要得到的是:

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

I managed to get this :

我设法得到了这个:

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

with this query :

使用此查询:

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

I use MySql

我使用MySql

3 个解决方案

#1

Seems like you need a correlated subquery.

好像你需要一个相关的子查询。

Assuming SQL Server, which is my original dialect, you could do something like this:

假设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;

3 个解决方案

#1

更多相关文章

随机推荐