在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;

#2

The query to get the thumbnail for the most viewed video in a channel would look vaguely like this, right?

获取频道中观看次数最多的视频的缩略图的查询看起来会像这样,对吧?

SELECT thumb.link FROM channel 
INNER JOIN video ON channel.id = video.channel_id
INNER JOIN thumb ON video.id = thumb.video_id
ORDER BY video.views DESC
LIMIT 1

It seems to me like the easiest way to do this is with a sub-query, so you can probably get the answer you want if you join your query with the one above, which should look something like this:

在我看来,最简单的方法是使用子查询,所以如果你将查询加入上面的查询,你可能得到你想要的答案,它应该是这样的:

SELECT channel.title, COUNT(*), 
(SELECT thumb.link FROM channel1 
INNER JOIN video1 ON channel1.id = video1.channel_id
INNER JOIN thumb ON video1.id = thumb.video_id
WHERE video1.id = video.id
ORDER BY video1.views DESC
LIMIT 1) AS thumb.link
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

But I'll admit that it usually takes me a bit of fiddling to get the subquery syntax right. I rarely get it on the first try.

但我承认,通常我需要一些摆弄才能使子查询语法正确。我很少在第一次尝试时得到它。

#3

Thank you both, I finnaly managed to get what I want with this query :

谢谢你们两个,我终于设法通过这个查询获得了我想要的东西:

SELECT c.title, COUNT(*),
(
    SELECT thumb.link
    FROM video
    INNER JOIN video_channel ON video_channel.video_id=video.id
    INNER JOIN thumb ON thumb.video_id=video_channel.video_id
    INNER JOIN channel ON channel.id=video_channel.channel_id
    WHERE video_channel.channel_id=c.id
    ORDER BY video.views DESC, thumb.id ASC
    LIMIT 1
) AS thumb_link
FROM channel c
INNER JOIN video_channel ON video_channel.channel_id=c.id
INNER JOIN video ON video.id=video_channel.video_id
GROUP BY video_channel.channel_id
ORDER BY COUNT(*) DESC

#1