从3个表中提取MySQL数据-联接和最大值 [英] MySQL data extraction from 3 tables - joins and max

问题描述

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

I have three mysql tables that I would like to extract some information from, the tables are:

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

我想做的是找到每个标签得分最高的视频.有很多具有相同标签的视频，但是我的结果集将具有 行数与标签相同.最终目标是为每个唯一标签(标签是带有前缀的主题作为话题)提供最佳视频列表(按得分).

What I want to do is find the videos with the highest points for each tag. There are many videos with the same tag, but my result set will have the same number of rows as there are tags. The end goal is to have a list of the best video (by points) for each unique tag (tags being a topic prefixed with a hash).

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

My SQL noob attempt at achieving this is as follows:

  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

以下是架构和示例数据:

Here's the schema and sample data:

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分，因此不是得分最高的视频 标记为222的标签.

Unfortunately the video_id for the second row is not what I expected, as owner-y-video-l does not have 44 points, rather it has 17 so would not be the highest scoring video for the tag with id 222.

有什么SQL Universe大师可以帮助我吗?谢谢一百万:)

Any Masters of the SQL Universe out there that can help me out? Thanks a million :)

推荐答案

您需要在 sqlfiddle 上查看.

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

Note that this query returns all videos having the maximum number of points within each tag, so more than one record will be returned for tied tags. If you wish to return only one record in such situations, please specify how to determine the video that should be returned.

这篇关于从3个表中提取MySQL数据-联接和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！