SQL：具有不同条件的多个计数语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14740975/
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: Multiple count statements with different criteria
提问by jj2
I was just wondering if there is any way to get two separate "count" totals from a table using one query? That is, using a table similar to the following I would like to retrieve each code (distinct) and show the total number of status' NOT equal to X or D, and then have an additional column that shows the total number of status' equal to X or D and the cancel date is greater than a given date (say, the last 14 days).
我只是想知道是否有任何方法可以使用一个查询从表中获得两个单独的“计数”总数？也就是说，使用类似于以下的表，我想检索每个代码（不同的）并显示状态总数'不等于 X 或 D，然后有一个附加列显示状态总数'等于到 X 或 D 并且取消日期大于给定日期（例如，过去 14 天）。
Table:
桌子：
Code: Status Cancel_Date
-----------------------------------
AAA X 2012-02-01
AAA
BBB X 2012-02-01
AAA D 2012-01-01
AAA
BBB
BBB D 2012-02-01
BBB X 2012-01-01
Example result (based on the above data):
示例结果（基于上述数据）：
Code: TotalNotXorD TotalXorD
------------------------------------
AAA 2 1
BBB 1 2
TotalNotXorD: e.g.
TotalNotXorD：例如
select code, count(*)
from table
where status not in('X','D')
group by code
TotalXorD: e.g.
TotalXorD：例如
select code, count(*)
from table
where status in('X','D')
and cancel_date >= '2012-02-01'
group by code
I have looked at doing subqueries etc. but I can't seem to get the results I need.
我看过做子查询等，但我似乎无法得到我需要的结果。
Any ideas?
有任何想法吗？
Thanks.
谢谢。
回答by John Woo
SELECT a.code,
COALESCE(b.totalNotXorD, 0 ) totalNotXorD,
COALESCE(c.totalXorD, 0 ) totalXorD,
FROM (SELECT DISTINCT Code FROM tableName) a
LEFT JOIN
(
select code, count(*) totalNotXorD
from table
where status not in('X','D')
group by code
) b ON a.code = b.code
LEFT JOIN
(
select code, count(*) totalXorD
from table
where status in('X','D')
and cancel_date >= '2012-02-01'
group by code
) c ON a.code = c.code
SELECT a.code,
COALESCE(b.totalNotXorD, 0 ) totalNotXorD,
COALESCE(c.totalXorD, 0 ) totalXorD,
FROM (SELECT DISTINCT Code FROM tableName) a
LEFT JOIN
(
select code, count(*) totalNotXorD
from table
where status not in('X','D')
group by code
) b ON a.code = b.code
LEFT JOIN
(
select code, count(*) totalXorD
from table
where status in('X','D')
and cancel_date >= '2012-02-01'
group by code
) c ON a.code = c.code
or simply doing CASE
或者干脆做 CASE
SELECT Code,
SUM(CASE WHEN status NOT IN ('X','D') OR status IS NULL THEN 1 ELSE 0 END) TotalNotXorD,
SUM(CASE WHEN status IN ('X','D') AND cancel_date >= '2012-02-01' THEN 1 ELSE 0 END) TotalXorD
FROM tableName
GROUP BY Code
