COUNT()、SUM()、AVG()、MAX()、MIN()和GROUP_CONCAT()函数中可以在参数前添加DISTINCT，表示对不重复的记录进行相关操作。

COUNT()的参数设置为“*”时，表示统计符合条件的所有记录(包含NULL)。

1.准备

CREATE DATABASE mahaiwuji;USE mahaiwuji;CREATE TABLE goods (   id INT,    name VARCHAR (32),    price INT) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO goods VALUES (1,'书',10);INSERT INTO goods VALUES (2,'键盘',11);INSERT INTO goods VALUES (3,'鼠标',15)INSERT INTO goods VALUES (4,'手机',20);INSERT INTO goods VALUES (5,NULL,15);

2.COUNT()

SELECT COUNT(id) FROM goods; -- 5SELECT COUNT(name) FROM goods; -- 4,NULL不统计SELECT COUNT(price) FROM goods; -- 5SELECT COUNT(DISTINCT price) FROM goods; -- 4,因为有2个15,不重复的数量为4SELECT COUNT(*) FROM goods; -- 5

3.SUM()

SELECT SUM(price) FROM goods; -- 71SELECT SUM(DISTINCT price) FROM goods; -- 56,因为有2个15,只算了一个

4.AVG()

SELECT AVG(price) FROM goods; -- 14.2SELECT AVG(DISTINCT price) FROM goods; -- 14,因为有2个15,只算了一个

5.MAX()

SELECT MAX(price) FROM goods; -- 20SELECT MAX(DISTINCT price) FROM goods; -- 20

6.MIN()

SELECT MIN(price) FROM goods; -- 10SELECT MIN(DISTINCT price) FROM goods; -- 10

7.GROUP_CONCAT()

-- 书,键盘,鼠标,手机SELECT GROUP_CONCAT(name) FROM goods;

8.JSON_ARRAYAGG()

-- ["书", "键盘", "鼠标", "手机", null]SELECT JSON_ARRAYAGG(name) FROM goods;

9.JSON_OBJECTAGG()

-- {"1": "书", "2": "键盘", "3": "鼠标", "4": "手机", "5": null}SELECT JSON_OBJECTAGG(id,name) FROM goods; -- error，因为id为5的name是NULL,JSON中键不能为NULLSELECT JSON_OBJECTAGG(name,price) FROM goods;