sql - Postgres - 仅选择具有 FAILURE 状态的日期

Question

对于特定的 project_name（SUCCESS、FAILURE、ABORTED、UNSTABLE），我每天都有多个结果的表。我只想选择仅出现 FAILURE 状态的日子 - 仅此而已。有些日子有多个状态（在失败后可以解决并获得不同时间的状态并设置为成功）。如何仅选择 FAILURE 状态的日子？

我的查询：

SELECT
    date_trunc('day', time) AS group_day,
    build_result,
    project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY date_trunc('day', time), project_name, build_result

标签： sqlpostgresqlgroup-byhaving

Answer 1

使用聚合并具有：

SELECT date_trunc('day', time) AS group_day, project_name
FROM project_status
WHERE project_name = 'project_x'
GROUP BY date_trunc('day', time), project_name
HAVING MIN(build_result) = MAX(build_result) AND
       MIN(build_result) = 'FAILURE'