java中mysql分组查询_二十六、MySQL GROUP BY 分组查询数据

GROUP BY 语句根据一个或多个列对结果集进行分组

在分组的列上可以使用 COUNT(), SUM(), AVG() 等函数

GROUP BY 语法格式

SQL SELECT 中语句使用 GROUP BY 子句对查询数据进行分组的语法格式如下

SELECT column_name, function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

范例数据

可以在 mysql> 命令行中运行以下语句填充范例数据

DROP TABLE IF EXISTS `tbl_language`;

DROP TABLE IF EXISTS `tbl_rank`;

CREATE TABLE IF NOT EXISTS `tbl_language`(

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(64) NOT NULL,

`url` VARCHAR(128) NOT NULL,

`founded_at` DATE,

PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tbl_rank`(

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(64) NOT NULL,

`month` VARCHAR(7) NOT NULL,

`rank` TINYINT NOT NULL,

`rate` VARCHAR(32) NOT NULL,

PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tbl_language` VALUES

(1,'Python','https://tech.souyunku.com','1991-2-20'),

(2,'PHP','http://www.php.net','1994-1-1'),

(3,'Ruby','https://www.ruby-lang.org/','1996-12-25');

INSERT INTO `tbl_rank` VALUES

(1, 'Python','2018-04',4,'5.083%'),

(2, 'PHP','2018-04',6,'4.218%'),

(3, 'Ruby','2018-04',11,'2.018%'),

(4, 'Java','2018-04',1,'15.777%'),

(5, 'Python','2018-03',4,'5.869%'),

(6, 'PHP','2018-03',7,'4.010%'),

(7, 'Ruby','2018-03',12,'2.744%'),

(8, 'Java','2018-03',1,'14.941'),

(9, 'Python','2018-02',4,'5.168%'),

(10, 'PHP','2018-02',7,'3.420%'),

(11, 'Ruby','2018-02',10,'2.534%'),

(12, 'Java','2018-02',1,'14.988%');

tbl_language 表中的数据如下

+----+--------+----------------------------+------------+

| id | name | url | founded_at |

+----+--------+----------------------------+------------+

| 1 | Python | https://tech.souyunku.com | 1991-02-20 |

| 2 | PHP | http://www.php.net | 1994-01-01 |

| 3 | Ruby | https://www.ruby-lang.org/ | 1996-12-25 |

+----+--------+----------------------------+------------+

tbl_rank 表中的数据如下

+----+--------+---------+------+---------+

| id | name | month | rank | rate |

+----+--------+---------+------+---------+

| 1 | Python | 2018-04 | 4 | 5.083% |

| 2 | PHP | 2018-04 | 6 | 4.218% |

| 3 | Ruby | 2018-04 | 11 | 2.018% |

| 4 | Java | 2018-04 | 1 | 15.777% |

| 5 | Python | 2018-03 | 4 | 5.869% |

| 6 | PHP | 2018-03 | 7 | 4.010% |

| 7 | Ruby | 2018-03 | 12 | 2.744% |

| 8 | Java | 2018-03 | 1 | 14.941 |

| 9 | Python | 2018-02 | 4 | 5.168% |

| 10 | PHP | 2018-02 | 7 | 3.420% |

| 11 | Ruby | 2018-02 | 10 | 2.534% |

| 12 | Java | 2018-02 | 1 | 14.988% |

+----+--------+---------+------+---------+

使用 GROUP BY 语句对 tbl_rank 中的 name 进行分组

可以使用下面的 GROUP BY 语句将数据表按 name 进行分组，并统计每个 name 有多少条记录

SELECT name, COUNT(*) FROM tbl_rank GROUP BY name;

运行结果如下

MariaDB [souyunku]> SELECT name, COUNT(*) FROM tbl_rank GROUP BY name;

+--------+----------+

| name | COUNT(*) |

+--------+----------+

| Java | 3 |

| PHP | 3 |

| Python | 3 |

| Ruby | 3 |

+--------+----------+

4 rows in set (0.01 sec)

可以使用下面的 GROUP BY 语句将数据表按 name 进行分组，并统计每个 name 的平均排名

SELECT name, AVG(rank) FROM tbl_rank GROUP BY name;

运行结果如下

MariaDB [souyunku]> SELECT name, AVG(rank) FROM tbl_rank GROUP BY name;

+--------+-----------+

| name | AVG(rank) |

+--------+-----------+

| Java | 1.0000 |

| PHP | 6.6667 |

| Python | 4.0000 |

| Ruby | 11.0000 |

+--------+-----------+

4 rows in set (0.01 sec)

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计 ( SUM,AVG,COUNT ...)

例如下面的语句将表 tbl_rank 按 name 进行分组，并统计每个 name 的平均排名，然后统计所有语言的平均排名

SELECT name, AVG(rank) FROM tbl_rank GROUP BY name WITH ROLLUP;

运行结果如下

MariaDB [souyunku]> SELECT name, AVG(rank) FROM tbl_rank GROUP BY name WITH ROLLUP;

+--------+-----------+

| name | AVG(rank) |

+--------+-----------+

| Java | 1.0000 |

| PHP | 6.6667 |

| Python | 4.0000 |

| Ruby | 11.0000 |

| NULL | 5.6667 |

+--------+-----------+

5 rows in set (0.00 sec)

其中记录 NULL 表示所有语言的平均排名

coalesce()

可以使用 coalesce() 来设置一个可以取代 NUll 的名称

coalesce() 语法格式如下

select coalesce(a,b,c);

参数说明

1、 如果 a==null 则选择 b

2、 如果 b==null 则选择 c

3、 如果 a!=null 则选择 a

4、 如果 a b c 都为 null ，则返回为 null (没意义)

下面的 SQL 语句使用 total 来代替 null

SELECT coalesce(name,'count'), AVG(rank) FROM tbl_rank GROUP BY name WITH ROLLUP;

运行结果如下

MariaDB [souyunku]> SELECT coalesce(name,'count'), AVG(rank) FROM tbl_rank GROUP BY name WITH ROLLUP;

+------------------------+-----------+

| coalesce(name,'count') | AVG(rank) |

+------------------------+-----------+

| Java | 1.0000 |

| PHP | 6.6667 |

| Python | 4.0000 |

| Ruby | 11.0000 |

| count | 5.6667 |

+------------------------+-----------+

5 rows in set (0.00 sec)

干货推荐

附录：MySQL 教程：系列文章