Mysql 仅当其所有子项都被选中时才返回一行

Question

我有这张表:

CREATE TABLE `test` (
    `id` VARCHAR(50) NOT NULL,
    `parent` VARCHAR(50) NULL DEFAULT NULL,
    `true` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_test_test` (`parent`),
    CONSTRAINT `FK_test_test` FOREIGN KEY (`parent`) REFERENCES `test` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

我有这个数据:

INSERT INTO `test` (`id`, `parent`, `true`) VALUES
('.', '.', NULL),
('active1', 'if', NULL),
('active2', 'if', NULL),
('check1', 'active1', 'case1'),
('check2', 'active1', 'case2'),
('check5', 'active1', 'case4'),
('check3', 'active2', 'case1'),
('check4', 'active2', 'case3'),
('if', '.', NULL);

我有这个问题:

SELECT a.`parent` FROM test a
INNER JOIN (test b) ON a.`parent` = b.`id` AND b.`parent` = 'if'
WHERE a.`true` IN ('case1','case2', 'case4')
GROUP BY a.`parent`;

在结果集中，我得到了 active1, active2，但我应该只有 active1，因为只有在以下情况下我才需要 a.parent 行它的所有 child 都被选中。

只有选择了它的所有子项，我怎样才能得到一行？

Answer 1

您可以使用 HAVING 并只获取与条件数相同的条件(在本例中为 2)

SELECT a.`parent` FROM test a
INNER JOIN (test b) ON a.`parent` = b.`id` AND b.`parent` = 'if'
WHERE a.`true` IN ('case1', 'case2')
GROUP BY a.`parent`
HAVING COUNT(a.`parent`) = 2;

一般意义上，您可以这样查看计数:

SELECT a.`parent` FROM test a
INNER JOIN (test b) ON a.`parent` = b.`id` AND b.`parent` = 'if'
WHERE a.`true` IN ('case1', 'case2')
GROUP BY a.`parent`
HAVING COUNT(a.`parent`) = 
  (SELECT COUNT(*) FROM test t1 WHERE t1.`parent` = a.`parent`);

SQL Fiddle demo