Mysql复杂选择

Question

如何选择具有精确子目录 ​​ID 的行？例如，我只需要选择 subcat_id 2,6 或 2,4,5,7 的行。

CREATE TABLE IF NOT EXISTS `testing123` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) DEFAULT '0',
  `subcat_id` int(10) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8;

REPLACE INTO `testing123` (`id`, `cat_id`, `subcat_id`) VALUES
    (170, 32, 5),
    (171, 33, 1),
    (172, 33, 7),
    (173, 34, 5),
    (175, 35, 2),
    (176, 36, 1),
    (177, 36, 2),
    (178, 36, 7),
    (179, 37, 2),
    (180, 37, 6),
    (184, 40, 2),
    (185, 40, 5),
    (186, 40, 6),
    (187, 41, 4),
    (188, 54, 2),
    (189, 54, 4),
    (190, 54, 5),
    (191, 54, 7),
    (192, 55, 6),
    (193, 56, 2),
    (194, 56, 6),
    (195, 57, 6),
    (196, 58, 1),
    (197, 59, 3),
    (198, 60, 6),
    (199, 61, 2),
    (200, 61, 6),
    (203, 63, 1),
    (204, 63, 6),
    (205, 64, 2),
    (206, 64, 6),
    (207, 65, 1),
    (208, 65, 2),
    (209, 66, 4),
    (214, 67, 1),
    (215, 67, 5),
    (220, 70, 1),
    (221, 70, 4),
    (222, 70, 5);

如果我使用 OR 或 IN()，它会选择所有提供任何子目录的行，但我只需要完全匹配。

为实验提供了表格示例。

救命啊! :)

Answer 1

SELECT *
  FROM testing123
 WHERE cat_id IN (SELECT cat_id
                    FROM testing123
                   WHERE subcat_id IN (2, 6)
                GROUP BY cat_id
                  HAVING COUNT(*) = 2)


SELECT *
  FROM testing123
 WHERE cat_id IN (SELECT cat_id
                    FROM testing123
                   WHERE subcat_id IN (2, 4, 5, 7)
                GROUP BY cat_id
                  HAVING COUNT(*) = 4)

请注意，COUNT(*) = N 子句取决于 IN() 中枚举了多少个子目录