查询含全部指定症状的疾病：症状名称查询失效及无COUNT聚合实现

首先，你的第二条SQL语句无法运行的核心原因是表连接的顺序和条件逻辑错误：你先从simptomat表开始左连接rel，但连接条件里引用了还未被关联的semundjet表字段，这会直接触发语法错误；另外，左连接的使用也会引入不必要的NULL行，干扰后续的计数判断。

先修复用症状名称的查询（保留HAVING的方式） #

先把表连接逻辑修正，应该从疾病表semundjet出发，关联关系表rel，再关联症状表simptomat，这样才能正确匹配疾病和对应的症状：

SELECT d.semundjeName
FROM semundjet d
JOIN rel r ON d.semundjeID = r.semundjeID
JOIN simptomat s ON r.simptomaID = s.simptomaID
WHERE s.simptomaName IN ('Merzi','Dhimbje Koke','Gjakederdhje','Dhimbje Fyti','Dhimbje Kycesh')
GROUP BY d.semundjeName
HAVING COUNT(DISTINCT s.simptomaName) = 5;

这里用COUNT(DISTINCT ...)是为了避免同一疾病重复关联同一症状的情况（虽然你的测试数据里没有，但写法更严谨）。

不用HAVING COUNT(*)的实现方式 #

如果你想完全避免使用HAVING COUNT，可以用以下两种直观的方式实现：

方法1：多EXISTS子查询逐个验证

这种方式逻辑直白，每一个EXISTS都单独检查当前疾病是否包含对应的症状，只有当所有症状都满足时才返回该疾病：

SELECT d.semundjeName
FROM semundjet d
WHERE EXISTS (
    SELECT 1
    FROM rel r
    JOIN simptomat s ON r.simptomaID = s.simptomaID
    WHERE r.semundjeID = d.semundjeID
    AND s.simptomaName = 'Merzi'
)
AND EXISTS (
    SELECT 1
    FROM rel r
    JOIN simptomat s ON r.simptomaID = s.simptomaID
    WHERE r.semundjeID = d.semundjeID
    AND s.simptomaName = 'Dhimbje Koke'
)
AND EXISTS (
    SELECT 1
    FROM rel r
    JOIN simptomat s ON r.simptomaID = s.simptomaID
    WHERE r.semundjeID = d.semundjeID
    AND s.simptomaName = 'Gjakederdhje'
)
AND EXISTS (
    SELECT 1
    FROM rel r
    JOIN simptomat s ON r.simptomaID = s.simptomaID
    WHERE r.semundjeID = d.semundjeID
    AND s.simptomaName = 'Dhimbje Fyti'
)
AND EXISTS (
    SELECT 1
    FROM rel r
    JOIN simptomat s ON r.simptomaID = s.simptomaID
    WHERE r.semundjeID = d.semundjeID
    AND s.simptomaName = 'Dhimbje Kycesh'
);

方法2：反向排除法（NOT EXISTS嵌套）

这个逻辑是：找出不存在“指定症状中没有被当前疾病关联”的情况，也就是所有指定症状都被疾病关联了：

SELECT d.semundjeName
FROM semundjet d
WHERE NOT EXISTS (
    SELECT s.simptomaName
    FROM simptomat s
    WHERE s.simptomaName IN ('Merzi','Dhimbje Koke','Gjakederdhje','Dhimbje Fyti','Dhimbje Kycesh')
    AND NOT EXISTS (
        SELECT 1
        FROM rel r
        WHERE r.semundjeID = d.semundjeID
        AND r.simptomaID = s.simptomaID
    )
);

验证结果 #

以上两种不用HAVING COUNT的方法，都能正确返回你的预期结果：Kancer。

内容的提问来源于stack exchange，提问作者tyrofoam treminguey