具有多个条件的多个SQL计数

I've been trying to optimise the way I retrieve data from my database for display on a "dashboard" type of page for software development

我一直在尝试优化从数据库中检索数据的方式，以便在软件开发的“仪表板”类型的页面上显示

My database structure is as follows:

我的数据库结构如下：

Requirements Table that contains all the various requirements with various fields, but importantly a REQ_ID as key.
要求表包含各种字段的所有各种要求，但重要的是REQ_ID作为关键字。
Tasks Table that contains can contain multiple tasks with a TASK_ID, TASK_NAME (DEV, TEST OR RELEASE), TASK_STATUS (Not Started, Complete, Blocked), TASK_WINDOW (Week1, Week2, .. etc. when task was completed) and a link back to a requirement with REQ_I. For example, a requirement may have multiple dev tasks, test tasks and release tasks but for can only be dev complete if all the dev tasks related to a requirement is complete, otherwise it is incomplete
任务包含的表可以包含多个任务，包括TASK_ID，TASK_NAME（DEV，TEST或RELEASE），TASK_STATUS（未启动，完成，已阻止），TASK_WINDOW（第1周，第2周，...等任务完成时）和链接返回与REQ_I的要求。例如，一个需求可能有多个开发任务，测试任务和发布任务，但只有在与需求相关的所有开发任务完成时才能完成开发，否则它是不完整的

I would like to query these two tables to provide me a results set that contains individually the number DEV Complete, Test Complete and Release Complete requirements per DEV task window in a single query. I'm currently performing multiple query each containing subqueries and then aggregating the results with PHP, however this in total takes 15 sec to exec, Can anybody please help me in consolidating this into a single query>

我想查询这两个表，以便为我提供一个结果集，其中包含单个查询中每个DEV任务窗口的DEV Complete，Test Complete和Release Complete要求的编号。我目前正在执行多个查询，每个查询都包含子查询，然后使用PHP聚合结果，但总共需要15秒才能执行，任何人都可以帮我将其合并到一个查询中>

2 个解决方案

#1

SELECT r.REQ_ID, 
       SUM(CASE WHEN t.TASK_NAME = 'DEV' THEN 1 ELSE 0 END) AS DevComplete,
       SUM(CASE WHEN t.TASK_NAME = 'TEST' THEN 1 ELSE 0 END) AS TestComplete,
       SUM(CASE WHEN t.TASK_NAME = 'RELEASE' THEN 1 ELSE 0 END) AS ReleaseComplete
    FROM Requirements r
        INNER JOIN Tasks t
            ON r.REQ_ID = t.REQ_ID
    WHERE t.TASK_STATUS = 'Complete'
    GROUP BY r.REQ_ID

#2

I realize this is an old question but I ran a test with the following patterns:

我意识到这是一个老问题，但我运行了以下模式的测试：

Pattern 1:

模式1：

SELECT
 [Count1] = SUM(CASE WHEN ... THEN 1 ELSE 0 END),
 [Count2] = SUM(CASE WHEN ... THEN 1 ELSE 0 END)
FROM
 [Table]
GROUP BY
 [Field]

Pattern 2:

模式2：

SELECT
  [COUNT1] = (SELECT COUNT(*) FROM [Table] WHERE ...),
  [Count2] = (SELECT COUNT(*) FROM [Table] WHERE ...)

In my case, when running both queries, pattern 2 took 36 % of the time and pattern 1 took 64%. To me, pattern 1 looks more elegant, but it didn't perform nearly as well in my scenario.

在我的例子中，当运行两个查询时，模式2占用了36％的时间而模式1占用了64％。对我来说，模式1看起来更优雅，但在我的场景中它的表现几乎没有。

#1

SELECT r.REQ_ID, 
       SUM(CASE WHEN t.TASK_NAME = 'DEV' THEN 1 ELSE 0 END) AS DevComplete,
       SUM(CASE WHEN t.TASK_NAME = 'TEST' THEN 1 ELSE 0 END) AS TestComplete,
       SUM(CASE WHEN t.TASK_NAME = 'RELEASE' THEN 1 ELSE 0 END) AS ReleaseComplete
    FROM Requirements r
        INNER JOIN Tasks t
            ON r.REQ_ID = t.REQ_ID
    WHERE t.TASK_STATUS = 'Complete'
    GROUP BY r.REQ_ID

#2