# MySQL中的统计查询案例

139 阅读3分钟

## 准备工作

``````CREATE TABLE stu(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
level VARCHAR(255) NOT NULL,
INDEX name_index(name)
);
``````

``````INSERT INTO stu(name, level, grades) VALUES ("一号", "A", 95);
INSERT INTO stu(name, level, grades) VALUES ("二号", "A", 85);
INSERT INTO stu(name, level, grades) VALUES ("三号", "D", 55);
INSERT INTO stu(name, level, grades) VALUES ("四号", "B", 85);
INSERT INTO stu(name, level, grades) VALUES ("五号", "A", 95);
INSERT INTO stu(name, level, grades) VALUES ("六号", "C", 55);
INSERT INTO stu(name, level, grades) VALUES ("七号", "C", 65);
INSERT INTO stu(name, level, grades) VALUES ("八号", "B", 85);
``````

## 正文

### 一. COUNT的简单使用

COUNT() 是一个聚合函数，用于统计行数

COUNT(1)统计不为NULL的记录行数
COUNT(*)统计所有记录行数
COUNT(字段)统计某个字段不为NULL的记录行数
COUNT(DISTINCT 字段)统计某个字段不为NULL的不同值的数量

``````SELECT COUNT(*) FROM stu;
``````
COUNT(*)
8
``````SELECT COUNT(grades) FROM stu;
``````
8
``````SELECT COUNT(DISTINCT grades) FROM stu;
``````
4

### 二. 统计查询案例

#### `案例1`：统计每个level分组里grades不为NULL的记录数

``````SELECT level, count(grades) FROM stu GROUP BY level;
``````

A3
B2
C2
D1

#### `案例2`：查询出level重复的数据

``````SELECT level FROM stu GROUP BY level HAVING COUNT(*) > 1;
``````
level
A
B
C

``````SELECT * FROM stu WHERE level IN (SELECT level FROM stu GROUP BY level HAVING COUNT(*) > 1);
``````

1一号A95
2二号A85
4四号B85
5五号A95
6六号C55
7七号C65
8八号B85

#### `案例3`：查询出level和grades均重复的数据

``````SELECT level, grades FROM stu GROUP BY level, grades HAVING COUNT(*) > 1;
``````

``````SELECT
*
FROM
stu
WHERE
IN
(SELECT level, grades FROM stu GROUP BY level, grades HAVING COUNT(*) > 1);
``````

#### `案例4`：查询出level的多余重复的数据

``````SELECT MIN(id) FROM stu GROUP BY level;
``````
MIN(id)
1
4
6
3

``````SELECT * FROM stu WHERE id NOT IN (SELECT MIN(id) FROM stu GROUP BY level);
``````

2二号A85
5五号A95
7七号C65
8八号B85

#### `案例5`：删除level的多余重复的数据

``````DELETE FROM stu WHERE id NOT IN (SELECT MIN(id) FROM stu GROUP BY level);
``````

[Err] 1093 - You can't specify target table 'stu' for update in FROM clause

``````DELETE FROM
stu
WHERE
id
NOT IN
(SELECT mit.mi FROM (SELECT min(id) AS mi FROM stu GROUP BY level) AS mit);
``````