JOIN和GROUP BY在4个表上的复杂SQL查询

问题描述：

'视频'表包含所有视频的列表。

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

“通道”表包含所有可能的通道。这与使用'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

MySQL或SQL服务器 - 这是什么呢？或者你想要一个ANSI答案？ –

答

谢谢两位，我finnaly设法得到我想要这个查询：

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

答

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

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

我使用MySql我刚刚编辑我的帖子。你有在MySQL的CROSS APPLY替代吗？ – Boolangery

我不知道，但后来我搜查：https://dev.mysql.com/doc/refman/5.5/en/correlated-subqueries.html –

答

的查询来获取缩略图的频道观看次数最多的视频看起来依稀这样的，对不对？

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

在我看来，像这样做的最简单的方法是使用一个子查询，所以你可能可以得到你想要的答案，如果你与上面的一个，它应该是这个样子加入您的查询：

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

但我承认，它通常需要我一点儿摆弄子查询语法正确。我很少在第一次尝试中获得它。

我会尝试今晚 – Boolangery

JOIN和GROUP BY在4个表上的复杂SQL查询

相关推荐