MySQL 选择为

Question

是否有办法实现以下目标？

以下查询仅用于传达意图。我确实理解它在语法上不正确。

SELECT 
        T_DATE,
        T_NAME,
        (SELECT MAX(DUR) WHERE STATUS = 'Y') AS UPD_MAX,
        (SELECT AVG(DUR) WHERE STATUS = 'Y') AS UPD_AVG,
        (SELECT MAX(DUR) WHERE STATUS = 'N') AS READ_MAX,
        (SELECT AVG(DUR) WHERE STATUS = 'N') AS READ_AVG
FROM
         TABLE_1
WHERE
         T_NAME LIKE 'ab%cd%'
GROUP BY 
         T_DATE,
         T_NAME

Answer 1

使用条件聚合:

SELECT T_DATE, T_NAME,
       MAX(CASE WHEN STATUS = 'Y' THEN DUR END)  AS UPD_MAX,
       AVG(CASE WHEN STATUS = 'Y' THEN DUR END)  AS UPD_AVG,
       MAX(CASE WHEN STATUS = 'N' THEN DUR END)  AS READ_MAX,
       AVG(CASE WHEN STATUS = 'N' THEN DUR END)  AS READ_AVG
FROM TABLE_1
WHERE T_NAME LIKE 'ab%cd%'
GROUP BY T_DATE, T_NAME;