是否可以在Count()中指定条件?

SELECT COUNT(*) FROM bla WHERE Position = 'Manager'

我认为您可以使用一个简单的WHERE子句来只选择count some记录。

你的意思是:

SELECT Count(*) FROM YourTable WHERE Position = 'Manager'

如果是这样，那么是的，这是可行的!

假设你不想限制返回的行，因为你也在聚合其他值，你可以这样做:

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount
from ...

假设在同一列中，你有经理，主管和团队领导的值，你可以得到每一个的计数如下所示:

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount,
count(case when Position = 'Supervisor' then 1 else null end) as SupervisorCount,
count(case when Position = 'Team Lead' then 1 else null end) as TeamLeadCount,
from ...

取决于你的意思，但另一种解释的意思是，你想要计数行与某个值，但不希望限制SELECT只是那些行…

SELECT SUM(CASE WHEN Position = 'Manager' THEN 1 ELSE 0 END) AS ManagerCount,
SUM(CASE WHEN Position = 'CEO' THEN 1 ELSE 0 END) AS CEOCount
FROM SomeTable

如果你不能用where子句限制查询本身，你可以使用count聚合只计算非空值的事实:

select count(case Position when 'Manager' then 1 else null end)
from ...

你也可以以类似的方式使用sum聚合:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

如果使用SQL 2005或更高版本，也可以使用Pivot关键字

更多信息和技网

SELECT *
FROM @Users
PIVOT (
COUNT(Position)
FOR Position
IN (Manager, CEO, Employee)
) as p

测试数据集

DECLARE @Users TABLE (Position VARCHAR(10))
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('CEO')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')

我知道这是非常古老的，但我喜欢这种情况下的NULLIF技巧，到目前为止我没有发现任何缺点。看看我的复制粘贴示例，虽然不是很实用，但演示了如何使用它。

NULLIF可能会对性能产生轻微的负面影响，但我想它仍然应该比子查询快。

DECLARE @tbl TABLE ( id [int] NOT NULL, field [varchar](50) NOT NULL)


INSERT INTO @tbl (id, field)
SELECT 1, 'Manager'
UNION SELECT 2, 'Manager'
UNION SELECT 3, 'Customer'
UNION SELECT 4, 'Boss'
UNION SELECT 5, 'Intern'
UNION SELECT 6, 'Customer'
UNION SELECT 7, 'Customer'
UNION SELECT 8, 'Wife'
UNION SELECT 9, 'Son'


SELECT * FROM @tbl


SELECT
COUNT(1) AS [total]
,COUNT(1) - COUNT(NULLIF([field], 'Manager')) AS [Managers]
,COUNT(NULLIF([field], 'Manager')) AS [NotManagers]
,(COUNT(1) - COUNT(NULLIF([field], 'Wife'))) + (COUNT(1) - COUNT(NULLIF([field], 'Son'))) AS [Family]
FROM @tbl

欢迎提出意见:-)

@Guffa的回答很好，只要指出可能用IF语句更干净就行了

select count(IIF(Position = 'Manager', 1, NULL)) as ManagerCount
from ...

注意使用PrestoDB SQL(来自Facebook)，有一个快捷方式:

https://prestodb.io/docs/current/functions/aggregate.html

Count_if (x)→bigint

返回TRUE输入值的个数。这 function等价于count(CASE WHEN x THEN 1 END)

以下是我所做的，以获得一个数据集，其中包括每个集装箱内满足标准的总数和数量。让我来回答这个问题"有多少集装箱的货物超过X%大于51号"

select
Schedule,
PackageNum,
COUNT (UniqueID) as Total,
SUM (
case
when
Size > 51
then
1
else
0
end
) as NumOverSize
from
Inventory
where
customer like '%PEPSI%'
group by
Schedule, PackageNum

如果使用Postgres或SQLite，可以使用Filter子句来提高可读性:

SELECT
COUNT(1) FILTER (WHERE POSITION = 'Manager') AS ManagerCount,
COUNT(1) FILTER (WHERE POSITION = 'Other') AS OtherCount
FROM ...