SQL SELECT COUNT 函数

SQL COUNT 函数返回一个查询中列的数目。NULL值不计算在内。


SQL COUNT 语法

SELECT COUNT(expression) AS resultName
FROM 表名
WHERE conditions

expression 可以是 *, 列名 or DISTINCT 列名. 所有这三种表达式在MS SQL Server, Oracle 和 mySQL都被支持。

SQL COUNT 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Mary Fox Finance 78000
204 Joe Lanyon Finance 45800
205 Sally Daff Finance 57000
206 Grace Salter 62000
302 James Band 75000
303 Dona Earl Development 55000
304 Tony Oakes Development 49000

简单例子
我们想知道表中有多少职员(employees):

SELECT COUNT(*) AS NumEmployee
FROM Employees

结果会是 7:

NumEmployee
7

NULL列的例子
如果我们执行下面的语句:

SELECT COUNT(Department) AS NumDepartment
FROM Employees

结果会是 5,因为Employees表中有2行记录Department的值为NULL:

NumDepartment
5

SELECT COUNT(DISTINCT 列名)的例子
如果我们想知道有多少个部门(Department):

SELECT COUNT(DISTINCT Department) AS NumDepartment
FROM Employees

结果会类似于:

NumDepartment
2

使用 WHERE 语句的例子
选择工资超过$50,000的职员人数:

SELECT COUNT(EmployeeId) AS NumEmployee
FROM Employees
WHERE Salary > 50000

结果会类似于:

NumEmployee
5

使用 GROUP BY 的例子
有时,我们也许会想知道每个部门(Department)中有多少职员(Employees):

SELECT Department, COUNT(EmployeeId) AS NumEmployee
FROM Employees
GROUP BY Department

结果会类似于:

Department NumEmployee
Finance 3
Development 2
NULL 2