mysql - Mysql仅在选择了所有子项后才返回一行

我有这张桌子：

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行。
只有选定了行的所有子级，才能获取行？

最佳答案

您可以使用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