SQL 如何最好地使用 CASE STATEMENT Count(*)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41132262/
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
How best to Count(*) with a CASE STATEMENT?
提问by procpy
The following SQL (on SQL Server) returns an error of:
以下 SQL（在 SQL Server 上）返回错误：
Incorrect syntax near '*'
'*' 附近的语法不正确
Is there something inherently wrong with using the following SELECT statement?:
使用以下 SELECT 语句是否存在固有的错误？：
SELECT
COUNT(CASE WHEN <conditions> THEN * ELSE NULL END) as conditionalcountall
FROM TABLE
I tried this variation which also failed:
我尝试了这种变体，但也失败了：
SELECT
CASE WHEN <conditions> THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM TABLE
回答by John Cappelletti
I tend to like sum()
我倾向于喜欢 sum()
SELECT
SUM(CASE WHEN <conditions> THEN 1 ELSE 0 END) as conditionalcountall
FROM TABLE
回答by ahmed abdelqader
Try This, it is Tested
试试这个，它已经过测试
SELECT
CASE WHEN 1 = 1 THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM TABLE
1 = 1is example conditions
1 = 1是示例条件
Demo:-
演示：-
Create table #temp (id int , col1 varchar (10))
go
insert into #temp values (1 , 'aaaa')
insert into #temp values (2 , 'bbbb')
insert into #temp values (3 , 'cccc')
SELECT
CASE WHEN 1 = 1 THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM #temp
Result:
结果：
In Case Condation like that id = 1you should select Count(*)in CASEcluse in your query
在这种情况下，id = 1您应该select Count(*)在CASE查询中包含这样的条件
like this:
像这样：
SELECT
CASE WHEN id = 1 THEN (select COUNT(*) from #temp) ELSE NULL END as conditionalcountall
FROM #temp
Result:-
结果：-
Note: if You used Count(*)directly, you counted the id column, so you should use group byas next:
注意：如果你Count(*)直接使用，你计算了id列，所以你应该使用group bynext：
SELECT
CASE WHEN id = 1 THEN COUNT(*) ELSE NULL END as conditionalcountall
FROM #temp
group by id
Result:
结果：
回答by Laughing Vergil
If you REALLY, REALLY want to use COUNT, then you can do this:
如果您真的，真的想使用COUNT，那么您可以这样做：
SELECT
COUNT(*)
FROM table
WHERE <conditions>
