在Case语句上显示SQL查询

Question

我正在运行下面的SQL，需要在Alert为YES时仅显示这些行。查询返回错误。

SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
    CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END as ALERT
FROM
    KPI
WHERE
    ALERT = 'YES'
    AND trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP,
    trunc(PERIOD_START)
ORDER BY
    trunc(period_start) desc,
    COMP,
    trunc(PERIOD_START)

输出仅显示突出显示的记录。

​

​

任何帮助都将不胜感激。

Answer 1

使用having过滤掉它们

SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC,
    CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END as ALERT
FROM
    KPI
WHERE
    trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP,
    trunc(PERIOD_START)
HAVING CASE WHEN COMP = 2
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.96 THEN 'YES' WHEN COMP = 3
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 1.62 THEN 'YES' WHEN COMP = 4
    AND round((sum(EC) / sum(mon)) * 100, 2) >= 2.06 THEN 'YES' ELSE 'NO' END = 'YES'
ORDER BY
    trunc(period_start) desc,
    COMP,
    trunc(PERIOD_START)

Answer 2

这里是另一种使用子查询的方法，在trunc(PERIOD_START)之后只排序/分组一次，并解码以消除7次和/轮等：

我假设subselect在这里是合适的，因为结果集不会太大……

SELECT COMP, DOW, Period_date, pct_ec, 'YES' as alert
FROM
(SELECT
    COMP,
    TO_CHAR(PERIOD_START, 'DAY') DOW,
    trunc(PERIOD_START) Period_Date,
    round((sum(EC) / sum(mon)) * 100, 2) Pct_EC
FROM
    KPI
WHERE
    trunc(period_start) >= trunc(sysdate -7)
    and trunc(period_start) < trunc(sysdate)
GROUP BY
    trunc(period_start),
    TO_CHAR(PERIOD_START, 'DAY'),
    COMP ) temp
WHERE 
   COMP in (2,3,4) 
       AND pct_ec >= decode( COMP, 2, 2.96, 3, 1.62, 4, 2.06, 0)   
ORDER BY
    period_date desc,
    COMP

db-fiddle