从3个表中提取MySQL数据-联接和最大值

Question

我有三个mysql表，我想从中提取一些信息，这些表是：

• 视频-代表带有分数的视频。
• 标签-包含标签的全局列表。
• VideoTags在视频和标签之间创建关联。

我想做的就是找到每个标签的得分最高的视频。有许多具有相同标签的视频，但是我的结果集将具有与标签相同的行数。最终目标是为每个唯一标签（标签是主题加上哈希值）提供最佳视频列表（按得分）。

我的SQL noob尝试实现此目标的方法如下：

  SELECT video.id AS video_id, video.owner_id, MAX(video.points), tag.id AS tag_id
    FROM Videos video, VideoTags videotag, Tags tag
   WHERE video.id = videotag.video_id
     AND videotag.tag_id = tag.id
     AND tag.content LIKE '#%'
GROUP BY tag.id

这是模式和示例数据：

DROP TABLE IF EXISTS `Video`;
CREATE TABLE `Video` (
  `id` varchar(24) NOT NULL default '',
  `owner_id` varchar(24) NOT NULL default '',
  `points` DOUBLE NOT NULL default 0
);

DROP TABLE IF EXISTS `Tags`;
CREATE TABLE `Tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(32) NOT NULL default ''
PRIMARY KEY (id)
);

DROP TABLE IF EXISTS `VideoTags`;
CREATE TABLE `VideoTags` (
  `video_id` varchar(24) NOT NULL default '',
  `tag_id` int(11) NOT NULL
);

INSERT INTO Videos (id,owner_id,points) VALUES ('owner-x-video-a','owner-x', 20);
INSERT INTO Videos (id,owner_id,points) VALUES ('owner-x-video-b','owner-x', 15);
INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-k','owner-y', 12);
INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-l','owner-y', 17);
INSERT INTO Videos (id,owner_id,points) VALUES ('owner-y-video-m','owner-y', 44);

INSERT INTO Tags (id, content) VALUES (111, '#topic-1');
INSERT INTO Tags (id, content) VALUES (222, '#topic-2');

INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-x-video-a',111);
INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-x-video-b',111);
INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-k',111);
INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-l',222);
INSERT INTO VideoTags (video_id,tag_id) VALUES ('owner-y-video-m',222);

我希望看到的是：

video_id          owner_id    MAX(video.points)  tag_id
owner-x-video-a   owner-x     20                 111
owner-y-video-m   owner-y     44                 222

但是我得到的是：

video_id          owner_id    MAX(video.points)  tag_id
owner-x-video-a   owner-x     20                 111
owner-y-video-l   owner-y     44                 222

不幸的是，第二行的video_id并非我所期望的，因为owner-y-video-
l没有44分，而是17分，因此对于ID为222的标签而言，得分最高的视频不是。

那里有SQL Universe大师可以帮助我吗？太感谢了 ：）

Answer 1

您想要按组的最大值：

SELECT * FROM Video JOIN (

  SELECT   VideoTags.tag_id, MAX(points) points
  FROM     Video JOIN VideoTags ON Video.id = VideoTags.video_id
  GROUP BY VideoTags.tag_id

) t USING (points) JOIN Tags ON t.tag_id = Tags.id

在sqlfiddle上看到它。

请注意，此查询返回的是每个标签中具有最大点数的 所有
视频，因此对于绑定的标签，将返回多个记录。在这种情况下，如果您只希望返回一条记录，请指定如何确定应返回的视频。