MySQL中的统计查询案例

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 3 天，点击查看活动详情

前言

相信在MySQL的实际使用过程中，COUNT()，DISTINCT() 和GROUP BY 都是常用，那么这三者结合，能够实现一些怎样的统计查询功能呢，本篇文章将逐步揭晓。

准备工作

创建一张stu表。

CREATE TABLE stu(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    level VARCHAR(255) NOT NULL,
    grades BIGINT 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;

COUNT(grades)
8

SELECT COUNT(DISTINCT grades) FROM stu;

COUNT(DISTINCT grades)
4

二. 统计查询案例

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

查询语句如下。

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

执行结果如下。

level	count(grades)
A	3
B	2
C	2
D	1

本案例中的SQL语句，是先对level进行分组，然后再统计每个level组里grades不为NULL的记录数。

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

首先如下SQL语句可以查询出level重复的数据的level值。

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

level
A
B
C

那么level值在上述查询结果中的记录就是目标记录，查询语句如下。

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

结果如下。

id	name	level	grades
1	一号	A	95
2	二号	A	85
4	四号	B	85
5	五号	A	95
6	六号	C	55
7	七号	C	65
8	八号	B	85

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

首先如下SQL语句可以查询出level和grades均重复的数据的level和grades值。

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

那么最终的查询语句如下所示。

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

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

首先如下语句可以查询出level的重复数据的最小id值。

SELECT MIN(id) FROM stu GROUP BY level;

MIN(id)
1
4
6
3

那么只要id不在上述查询结果中的记录，就是目标记录，SQL如下所示。

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

查询结果如下。

id	name	level	grades
2	二号	A	85
5	五号	A	95
7	七号	C	65
8	八号	B	85

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

首先想到的是通过如下语句来做删除。

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

但是运行上述SQL，会报错，报错信息如下。

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

也就是查询一个表的同时又更新这个表，MySQL是不支持这种操作方式的。

但如下的SQL可以实现功能且查询和更新不在同一张表上。

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

---

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 3 天，点击查看活动详情