sql - 从表中的不同条件中选择 COUNT

Question

我有一个名为“jobs”的表。对于特定用户，作业可以是事件的、存档的、过期的、待处理的或关闭的。现在，每个页面请求都会生成 5 COUNT 个查询，为了优化，我尝试将其减少为单个查询。这是我到目前为止所拥有的，但它只比 5 个单独的查询快一点。请注意，我简化了每个子查询的条件，以便更容易理解，但完整查询的行为是相同的。

有没有办法在同一个查询中获取这 5 个计数，而不使用低效的子查询？

SELECT
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.status_id NOT IN (8,3,11) /* 8,3,11 being 'inactive' related statuses */
  ) AS active_count, 
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.due_date < '2011-06-14' AND
      jobs.status_id NOT IN(8,11,5,3) /* Grabs the overdue active jobs
                                      ('5' means completed successfully) */
  ) AS overdue_count,
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000'
  ) AS due_today_count

这又需要两个子查询，但我想你已经明白了。

是否有更简单的方法来收集此数据，因为它基本上是来自作业表中的同一数据子集的 5 个不同的 COUNT？

数据的子集是“creator_id = 5”，之后每个计数基本上只是 1-2 个附加条件。请注意，目前我们正在使用 Postgres，但可能会在不久的将来转向 MySQL。因此，如果您能提供 ANSI 兼容的解决方案，我将不胜感激:)

Answer 1

这是典型的解决方案。使用 case 语句来区分不同的条件。如果一条记录满足，则得到 1，否则得到 0。然后对值​​进行 SUM

  SELECT
    SUM(active_count) active_count,
    SUM(overdue_count) overdue_count
    SUM(due_today_count) due_today_count
  FROM 
  (

  SELECT 
    CASE WHEN jobs.status_id NOT IN (8,3,11) THEN 1 ELSE 0 END active_count,
    CASE WHEN jobs.due_date < '2011-06-14' AND jobs.status_id NOT IN(8,11,5,3)  THEN 1 ELSE 0 END  overdue_count,
    CASE WHEN jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000' THEN 1 ELSE 0 END  due_today_count

    FROM "jobs"
    WHERE
      jobs.creator_id = 5 ) t

更新如前所述，当返回 0 条记录时，这会导致所有值都为 Null 的单个结果。您有三个选择

1)添加一个having子句，这样你就没有返回任何记录，而不是所有NULL的结果

   HAVING SUM(active_count) is not null

2)如果您希望返回所有零，则可以将合并添加到所有总和中

例如

 SELECT
      COALESCE(SUM(active_count)) active_count,
       COALESCE(SUM(overdue_count)) overdue_count
      COALESCE(SUM(due_today_count)) due_today_count

3) 利用 sbarro 所演示的 COUNT(NULL) = 0 这一事实。您应该注意，非空值可以是任何值，不必是 1

例如

 SELECT
      COUNT(CASE WHEN 
            jobs.status_id NOT IN (8,3,11) THEN 'Manticores Rock' ELSE NULL
       END) as [active_count]