如何在SQL统计结果中新增对应状态的task_name列表列？

你可以在原查询的基础上，通过条件聚合+字符串拼接函数实现需求——针对每个状态，用CASE筛选出对应task_name，再用数据库支持的字符串聚合函数把这些名称拼接成列表，和对应的计数列放在一起。

以下是不同数据库的实现代码，你可以根据自己使用的数据库选择：

MySQL/MariaDB 版本 #

SELECT
    P.account_2,
    P.id,
    a.account,
    -- 统计NOT_RUN状态的任务数
    COUNT(CASE WHEN t1.status = 'NOT_RUN' THEN 1 END) AS not_run_count,
    -- 拼接NOT_RUN状态的所有任务名称
    GROUP_CONCAT(CASE WHEN t1.status = 'NOT_RUN' THEN t1.task_name END SEPARATOR ', ') AS not_run_tasks,
    -- 统计run状态的任务数
    COUNT(CASE WHEN t1.status = 'run' THEN 1 END) AS run_count,
    -- 拼接run状态的所有任务名称
    GROUP_CONCAT(CASE WHEN t1.status = 'run' THEN t1.task_name END SEPARATOR ', ') AS run_tasks,
    -- 统计failed状态的任务数
    COUNT(CASE WHEN t1.status = 'failed' THEN 1 END) AS failed_count,
    -- 拼接failed状态的所有任务名称
    GROUP_CONCAT(CASE WHEN t1.status = 'failed' THEN t1.task_name END SEPARATOR ', ') AS failed_tasks
FROM table1 t1
JOIN table2 P ON -- 替换成你实际的表关联条件
-- 这里补充你原来的其他JOIN语句
GROUP BY P.account_2, P.id, a.account

PostgreSQL 版本 #

SELECT
    P.account_2,
    P.id,
    a.account,
    COUNT(CASE WHEN t1.status = 'NOT_RUN' THEN 1 END) AS not_run_count,
    STRING_AGG(CASE WHEN t1.status = 'NOT_RUN' THEN t1.task_name END, ', ') AS not_run_tasks,
    COUNT(CASE WHEN t1.status = 'run' THEN 1 END) AS run_count,
    STRING_AGG(CASE WHEN t1.status = 'run' THEN t1.task_name END, ', ') AS run_tasks,
    COUNT(CASE WHEN t1.status = 'failed' THEN 1 END) AS failed_count,
    STRING_AGG(CASE WHEN t1.status = 'failed' THEN t1.task_name END, ', ') AS failed_tasks
FROM table1 t1
JOIN table2 P ON -- 替换成你实际的表关联条件
-- 这里补充你原来的其他JOIN语句
GROUP BY P.account_2, P.id, a.account

SQL Server 版本 #

SELECT
    P.account_2,
    P.id,
    a.account,
    COUNT(CASE WHEN t1.status = 'NOT_RUN' THEN 1 END) AS not_run_count,
    STRING_AGG(CASE WHEN t1.status = 'NOT_RUN' THEN t1.task_name END, ', ') AS not_run_tasks,
    COUNT(CASE WHEN t1.status = 'run' THEN 1 END) AS run_count,
    STRING_AGG(CASE WHEN t1.status = 'run' THEN t1.task_name END, ', ') AS run_tasks,
    COUNT(CASE WHEN t1.status = 'failed' THEN 1 END) AS failed_count,
    STRING_AGG(CASE WHEN t1.status = 'failed' THEN t1.task_name END, ', ') AS failed_tasks
FROM table1 t1
JOIN table2 P ON -- 替换成你实际的表关联条件
-- 这里补充你原来的其他JOIN语句
GROUP BY P.account_2, P.id, a.account

Oracle 版本 #

SELECT
    P.account_2,
    P.id,
    a.account,
    COUNT(CASE WHEN t1.status = 'NOT_RUN' THEN 1 END) AS not_run_count,
    LISTAGG(CASE WHEN t1.status = 'NOT_RUN' THEN t1.task_name END, ', ') WITHIN GROUP (ORDER BY t1.task_name) AS not_run_tasks,
    COUNT(CASE WHEN t1.status = 'run' THEN 1 END) AS run_count,
    LISTAGG(CASE WHEN t1.status = 'run' THEN t1.task_name END, ', ') WITHIN GROUP (ORDER BY t1.task_name) AS run_tasks,
    COUNT(CASE WHEN t1.status = 'failed' THEN 1 END) AS failed_count,
    LISTAGG(CASE WHEN t1.status = 'failed' THEN t1.task_name END, ', ') WITHIN GROUP (ORDER BY t1.task_name) AS failed_tasks
FROM table1 t1
JOIN table2 P ON -- 替换成你实际的表关联条件
-- 这里补充你原来的其他JOIN语句
GROUP BY P.account_2, P.id, a.account

额外说明 #

• 如果需要去掉重复的任务名称，可以在聚合函数里加DISTINCT，比如GROUP_CONCAT(DISTINCT CASE ...)。
• 分隔符可以自定义，把, 换成你需要的符号（比如; 、|）即可。
• Oracle的LISTAGG需要指定排序规则，你可以根据需求调整ORDER BY后面的字段。

内容的提问来源于stack exchange，提问作者suhas savkoor