SQL-查找满足条件组合的子集

Question

这个例子有点傻，但它基本上就是我要处理的。。。假设我有两个表，T_PARENT和T_CHILDREN，其中父母可以有0..N个孩子。表格结构和数据的简化示例如下：

• T_父母

IDPARENT	名称
1	Carlos

一个选项是使用以下条件聚合：

SELECT T.IDPARENT, T.NAME
FROM T_PARENT T JOIN T_CHILDREN D
ON T.IDPARENT = D.IDPARENT
GROUP BY T.IDPARENT, T.NAME
HAVING
COUNT(CASE WHEN D.NAME='Pablo' THEN 1 END) > 0 AND
COUNT(CASE WHEN D.NAME='Juan' AND D.AGE=9 THEN 1 END) > 0 AND
COUNT(CASE WHEN D.NAME='Diego' AND D.AGE=9 AND D.HEIGHT=110 THEN 1 END) > 0 AND
COUNT(CASE WHEN D.NAME='Diego' AND D.AGE=9 AND D.HEIGHT=120 THEN 1 END) > 0
ORDER BY T.IDPARENT, T.NAME

查看演示。

另一种选择是使用EXISTS运算符如下：

SELECT T.IDPARENT, T.NAME
FROM T_PARENT T 
WHERE EXISTS(SELECT 1 FROM T_CHILDREN D WHERE T.IDPARENT = D.IDPARENT AND D.NAME='Pablo')
AND EXISTS(SELECT 1 FROM T_CHILDREN D WHERE T.IDPARENT = D.IDPARENT AND  D.NAME='Juan' AND D.AGE=9)
AND EXISTS(SELECT 1 FROM T_CHILDREN D WHERE T.IDPARENT = D.IDPARENT AND D.NAME='Diego' AND D.AGE=9 AND D.HEIGHT=110)
AND EXISTS(SELECT 1 FROM T_CHILDREN D WHERE T.IDPARENT = D.IDPARENT AND D.NAME='Diego' AND D.AGE=9 AND D.HEIGHT=120)

查看演示。