MySQL嵌套查询HAVING字段

Question

我正在尝试配置子查询，但在聚合/分组方面遇到了一些困难。子查询根据平均值选择一个计数。但是，将 AVG(response) >= target 条件放在 WHERE block 中会导致“无效使用组函数”错误。将条件放在子查询的 HAVING block 中是可行的，除了它要求 target 变量必须出现在带有 COUNT 的 SELECT block 中，这导致“操作数应包含 1 列”错误。下面是查询的副本。

我熟悉嵌套查询和聚合函数，但这种特殊组合拒绝合作。似乎应该有一个关键字“选择此字段进行比较/拥有但不返回它”，或者我可能只是错误地制定了查询。不幸的是，这是由 BIRT 报告软件运行的，所以我不能(据我所知)获取原始数据并手动汇总。

查询(为清楚起见略微删减)如下:

SELECT 
    c.job_competency_id,
    (
        SELECT 
            COUNT(ar1.assessment_result_id) 
        FROM 
            recent_assessment AS ra1,
            assessment_result AS ar1,
            client_job_competency AS jc1
        WHERE 
            ar1.assessment_id = a.assessment_id
            AND ra1.assessment_id = ar1.assessment_id
            AND ar1.job_competency_id = jc1.job_competency_id
            AND ar1.job_competency_id = c.job_competency_id
            AND AVG(ar1.response) >= jc1.target
        GROUP BY jc1.job_competency_id
    ) AS n_meets
FROM 
    recent_assessment AS ra,
    assessment AS a,
    assessment_result AS ar,
    client_job_competency AS jc,
    job_competency AS c,
    master_competency AS mc 
WHERE 
    a.client_id = ?
    AND ra.assessment_id = a.assessment_id
    AND ar.assessment_id = a.assessment_id
    AND c.job_competency_id = ar.job_competency_id
    AND c.master_competency_id = mc.master_competency_id
    AND jc.job_competency_id = c.job_competency_id;

以防万一，替代子查询在这里:

    (
        SELECT 
            COUNT(ar1.assessment_result_id),
            jc1.target
        FROM 
            recent_assessment AS ra1,
            assessment_result AS ar1,
            client_job_competency AS jc1
        WHERE 
            ar1.assessment_id = a.assessment_id
            AND ra1.assessment_id = ar1.assessment_id
            AND ar1.job_competency_id = jc1.job_competency_id
            AND ar1.job_competency_id = c.job_competency_id
        GROUP BY jc1.job_competency_id
        HAVING AVG(ar1.response) >= jc1.target
    ) AS n_meets

如果您读到这里，感谢您的浏览。如果有任何需要澄清的地方，请告诉我。希望我只是在某个地方犯了一个愚蠢的错误。

Answer 1

您不能在 WHERE 子句中使用聚合函数，因为它直到选择了所有行后才处理聚合，这就是 WHERE > 条款确实如此。

要解决 HAVING 要求您选择要比较的列的问题，您可以将其包装在另一级别的子查询中。然后外部查询可以只返回您想要放入 SELECT 列表中的一列。

(SELECT n_meets
FROM (
    SELECT 
        COUNT(ar1.assessment_result_id) AS n_meets,
        jc1.target
    FROM 
        recent_assessment AS ra1,
        assessment_result AS ar1,
        client_job_competency AS jc1
    WHERE 
        ar1.assessment_id = a.assessment_id
        AND ra1.assessment_id = ar1.assessment_id
        AND ar1.job_competency_id = jc1.job_competency_id
        AND ar1.job_competency_id = c.job_competency_id
    GROUP BY jc1.job_competency_id
    HAVING AVG(ar1.response) >= jc1.target
) AS x) AS n_meets