mysql - 如何在mysql中获取每个视频的所有子类别

Question

我有这 3 个表:

CREATE TABLE `subcategories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `categories_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_subcategories_categories1_idx` (`categories_id`),
  CONSTRAINT `fk_subcategories_categories1` FOREIGN KEY (`categories_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8;

CREATE TABLE `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brightcove_id` varchar(45) DEFAULT NULL,
  `video_stillUrl` text,
  `video_thumbnailURL` text,
  `video_playsTotal` varchar(45) DEFAULT NULL,
  `establishments_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_videos_establishments1_idx` (`establishments_id`),
  CONSTRAINT `fk_videos_establishments1` FOREIGN KEY (`establishments_id`) REFERENCES `establishments` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8;

CREATE TABLE `subcategories_videos` (
  `subcategories_id` int(11) NOT NULL,
  `videos_id` int(11) NOT NULL,
  PRIMARY KEY (`subcategories_id`,`videos_id`),
  KEY `fk_subcategories_videos_subcategories1_idx` (`subcategories_id`),
  KEY `fk_subcategories_videos_videos1_idx` (`videos_id`),
  CONSTRAINT `fk_subcategories_videos_subcategories1` FOREIGN KEY (`subcategories_id`) REFERENCES `subcategories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_subcategories_videos_videos1` FOREIGN KEY (`videos_id`) REFERENCES `videos` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想知道，如何获取确定子类别中的所有视频，例如:

select * from subcategories_videos where subcategories_id in(62,61);

这里我获取了与 id 62 和 61 的类别相对应的所有视频，但是如何获取同时属于两个子类别的视频，如下所示:

select * from subcategories_videos where videos_id=193

这里它将通过视频 ID 获取子类别，但如何对所有记录执行此操作？非常感谢!

Answer 1

您可以使用 group by 和having

  select videos_id from subcategories_videos 
  where subcategories_id in(62,61)
  Group by videos_id
  Having count(*) = 2