database - 多个值的结果子查询 - SQL

Question

当我向 QUERY 添加多个客户名称时，我从 SUBQUERY 得到反馈，但如果您只输入一个值，即使使用 IN 条件，QUERY 完美运行

查询失败

SELECT "CUSTOMER",
    ( ( ( ( CAST(COUNT(*) AS FLOAT) ) - (
        SELECT
            (CAST(COUNT(*) AS FLOAT))
        FROM
            public.tbl_metrics
        WHERE
            "CUSTOMER" IN (
                'CLIENT1','CLIENT2','CLIENT3'
            )
            AND "STATUS" = 'NOT_TESTED'
        GROUP BY
            "STATUS",
            "CUSTOMER"
    ) ) / CAST(COUNT(*) AS INT) ) * 100) as "PERCENT", count("STATUS") as "OK"
FROM
    public.tbl_metrics
WHERE
    "CUSTOMER" IN (
        'CLIENT1','CLIENT2','CLIENT3'
    )
    AND "STATUS" = 'NOK'
GROUP BY
    "STATUS",
    "CUSTOMER"

查询成功

SELECT "CUSTOMER",
    ( ( ( ( CAST(COUNT(*) AS FLOAT) ) - (
        SELECT
            (CAST(COUNT(*) AS FLOAT))
        FROM
            public.tbl_metrics
        WHERE
            "CUSTOMER" IN (
                'CLIENT1'
            )
            AND "STATUS" = 'NOT_TESTED'
        GROUP BY
            "STATUS",
            "CUSTOMER"
    ) ) / CAST(COUNT(*) AS INT) ) * 100) as "PERCENT", count("STATUS") as "OK"
FROM
    public.tbl_metrics
WHERE
    "CUSTOMER" IN (
        'CLIENT1'
    )
    AND "STATUS" = 'NOK'
GROUP BY
    "STATUS",
    "CUSTOMER"

Answer 1

不太清楚“获得反馈”是什么意思？我猜你的子查询返回多行(因为内部查询分组多个客户 = 多个结果)。

我建议从一个更简单的版本开始，让它运行起来，然后添加额外的计算(即百分比)。这是一个粗略的入门查询(不保证这会起作用，因为我无权访问您的表并且我猜测这是您希望完成的)。

SELECT met."CUSTOMER",
    ( SELECT COUNT(*)
        FROM
            public.tbl_metrics innerMet
        WHERE
            innerMet."CUSTOMER"= met."CUSTOMER"
            AND "STATUS" = 'NOT_TESTED'
    ) as "NOT_TESTED", count("STATUS") as "OK"
FROM
    public.tbl_metrics met
WHERE
    met."CUSTOMER" IN (
        'CLIENT1','CLIENT2','CLIENT3'
    )
    AND met."STATUS" = 'NOK'
GROUP BY
    met."STATUS",
    met."CUSTOMER"