SQL SELECT 使用 CASE 语句，但有多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5496246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SELECT using CASE Statement, but multiple criteria
提问by john
I'm trying to perform a SQL SELECT query using a CASE statement, which is working 100%. My code looks as follows:
我正在尝试使用 CASE 语句执行 SQL SELECT 查询，该语句工作 100%。我的代码如下所示：
SELECT t.TASK_WINDOW,
SUM(CASE WHEN t.TASK_NAME = 'DEV' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS DevComplete,
SUM(CASE WHEN t.TASK_NAME = 'TEST' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS TestComplete,
SUM(CASE WHEN t.TASK_NAME = 'RELEASE' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS ReleaseComplete
FROM Requirements r
INNER JOIN Tasks t
ON r.TASK = t.REQ_ID
GROUP BY t.TASK_WINDOW
However my problem is that I have an additional criteria for all three SUMS. The complexity is that a requirement might have multiple tasks of each type, but for example an requirement must only be Dev Task complete if all its Dev Tasks are complete, otherwise it is incomplete.
但是我的问题是我对所有三个 SUMS 都有一个额外的标准。复杂性在于一个需求可能有每种类型的多个任务，但例如一个需求只有在其所有开发任务都完成时才必须是开发任务完成，否则它是不完整的。
I really want to be able to measure the amount of requirements with all its dev tasks complete, and also all its test tasks complete and all its release tasks complete, but group them all against the latest Dev Task compelted's window.
我真的希望能够在完成所有开发任务、完成所有测试任务和完成所有发布任务的情况下测量需求量，但将它们全部与最新的开发任务强制窗口进行分组。
Please help ;-)
请帮忙 ;-）
回答by Ben
You need to compare the total with the number complete:
您需要将总数与完成数进行比较：
SELECT t.TASK_WINDOW,
CASE
WHEN SUM(CASE WHEN t.TASK_NAME = 'DEV' THEN 1 ELSE 0 END) =
SUM(CASE WHEN t.TASK_NAME = 'DEV' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END)
Then 1
ELSE 0
END as AllDevComplete
FROM Requirements r
INNER JOIN Tasks t
ON r.TASK = t.REQ_ID
GROUP BY t.TASK_WINDOW
Alternatively you could look for the number which are NOT complete. But the same basic trick - a case of a sum of a case - will work.
或者，您可以查找不完整的数字。但同样的基本技巧 - 一个案例的总和 - 将起作用。
