COUNT

声明

COUNT({ * | [ DISTINCT | ALL ] expr }) OVER (analytic_clause)

说明

该函数返回 SELECT 语句检索到的行中非 NULL 值的数目。若找不到匹配的行，则 COUNT() 返回 0。DISTINCT 选项可用于返回 expr 的不同值的数目。

COUNT(*) 的不同之处在于，它返回检索行的数目，不论其是否包含 NULL 值。

示例

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.081 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

SELECT LAST_NAME, COUNT(*) OVER (PARTITION BY JOB_ID) COUNT_S  FROM EXPLOYEES;
+-----------+---------+
| LAST_NAME | COUNT_S |
+-----------+---------+
| JIM       |       1 |
| MIKE      |       3 |
| LILY      |       3 |
| TOM       |       3 |
+-----------+---------+
4 rows in set (0.001 sec)