时间戳

首页 > 解决方案 > 多个值的结果子查询 - SQL

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

问题描述

SUBQUERY当我向 中添加多个客户名称时，我收到了反馈QUERY，但如果您只输入一个值，即使使用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"

标签： databasepostgresql

解决方案

不清楚“获得反馈”是什么意思？我猜您的子查询返回的不止一行（因为内部查询将多个客户分组 = 多个结果）。

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

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"

推荐阅读