从表上的不同条件中选择COUNT

| 我有一个名为\'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

继续进行另外2个子查询，但我想您明白了。是否有一种更简单的方法来收集此数据，因为作业表中的同一数据子集基本上有5个不同的COUNT个？数据子集为\'creator_id = 5 \'，之后每个计数基本上只是1-2个附加条件。请注意，目前我们正在使用Postgres，但可能会在不久的将来转向MySQL。因此，如果您可以提供与ANSI兼容的解决方案，我将不胜感激:)

已邀请:

3 个回复

浆错

这是典型的解决方案。使用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记录作为t返回时，此结果将作为所有值中Null的单个结果。您有三种选择 1）添加一个Haveing子句，以便您没有返回记录，而不是所有NULLS的结果

   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]

厘恼轨

我将使用这种方法，将COUNT与CASE WHEN结合使用。

SELECT 
    COUNT(CASE WHEN 
        jobs.status_id NOT IN (8,3,11) THEN 1 
    END) as [Count1],
    COUNT(CASE WHEN 
        jobs.due_date < \'2011-06-14\' 
        AND jobs.status_id NOT IN(8,11,5,3) THEN 1
    END) as [COUNT2],
    COUNT(CASE WHEN
            jobs.due_date BETWEEN \'2011-06-14\' AND \'2011-06-15 06:00:00.000000\'
    END) as [COUNT3]
FROM 
    \"jobs\"
WHERE 
     jobs.creator_id = 5

敦肌

简要 SQL Server 2012引入了“ 8”逻辑功能。使用SQL Server 2012或更高版本，您现在可以使用此新函数代替CASE表达式。 IIF函数也可与Azure SQL数据库一起使用（但目前不适用于Azure SQL数据仓库或并行数据仓库）。它是CASE表达式的简写。当只有一种情况时，我发现自己使用的是IIF函数而不是CASE表达式。这样可以减轻写CASE WHEN condition THEN x ELSE y END而改成instead15ѭ的痛苦。如果可能满足多个条件（多个ѭ16s），则应考虑使用正则CASE表达式而不是嵌套的IIF函数。返回两个值之一，具体取决于布尔表达式是否在SQL Server中计算为true或false。句法

IIF ( boolean_expression, true_value, false_value )

争论 boolean_expression有效的布尔表达式。如果此参数不是布尔表达式，则语法错误为上调。 true_value如果boolean_expression计算得出的值，则返回真正。 false_value如果boolean_expression计算得出的值虚假。备注 IIF是编写a9ѭ表达式的简便方法。它评估作为第一个参数传递的布尔表达式，然后返回其他两个参数之一基于评价。也就是说，如果布尔值返回ѭ21 表达式为true，如果布尔值返回ѭ23 表达式错误或未知。 true_value和false_value可以是任何类型。适用于CASE表达式的相同规则布尔表达式，空值处理和返回类型也适用于 IIF。有关更多信息，请参见ѭ9（Transact-SQL）。将“ 8”翻译成“ 9”的事实也对此功能的行为的其他方面。自CASE 表达式最多只能嵌套10个IIF语句也可以嵌套，最多嵌套10个级别。此外，IIF是以语义上等效的方式“ 9”远程传输到其他服务器表达式，以及远程ѭ9表达式的所有行为。码 SQL中的“ 8”函数的实现类似于以下内容（使用@rsbarro在其答案中给出的相同逻辑）：

SELECT 
    COUNT(
        IIF(jobs.status_id NOT IN (8,3,11), 1, 0)
    ) as active_count,
    COUNT(
        IIF(jobs.due_date < \'2011-06-14\' AND jobs.status_id NOT IN(8,11,5,3), 1, 0)
    ) as overdue_count,
    COUNT(
        IIF(jobs.due_date BETWEEN \'2011-06-14\' AND \'2011-06-15 06:00:00.000000\', 1, 0)
    ) as due_today_count
FROM 
    \"jobs\"
WHERE 
     jobs.creator_id = 5

要回复问题请先登录或注册

从表上的不同条件中选择COUNT

3 个回复

发起人

sql

subquery

query_optimization

问题状态

从表上的不同条件中选择COUNT

与内容相关的链接

3 个回复

发起人

sql

subquery

query_optimization

问题状态