SQL学习之group by语句

参考源

• 简单教程
  • https://www.twle.cn/l/yufei/sql/sql-basic-groupby.html
• 菜鸟教程
  • https://www.runoob.com/sql/sql-groupby.html

SQL group by 语句

SQL 中的 group by 语句根据一个或多个列对结果集进行分组，一般配合聚合函数使用

select column_name, aggregate_function(column_name) from table_name where column_name operator value group by column_name;

示例数据

CREATE DATABASE IF NOT EXISTS hardy_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE hardy_db;

DROP TABLE IF EXISTS lesson;

CREATE TABLE lesson_views (
	id INT ( 11 ) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR ( 32 ) DEFAULT '',
	lession_id int(11) NOT NULL default 0,
	views INT ( 11 ) NOT NULL DEFAULT 0,
	created_at TIMESTAMP 
);

INSERT INTO lesson_views ( id, name, views,lesson_id, created_at )
VALUES
	( 1, 'Python3 基础教程', 981, 1, '2019-03-18 13:52:03' ),
	( 2, 'JavaScript 基础教程', 73, 2, '2019-03-18 16:03:32' ),
	( 3, 'Ruby 基础教程', 199, 3, '2019-04-01 06:16:14' ),
	( 4, 'SQL 基础教程', 533, 1, '2019-05-02 08:13:42' ),
	( 5, 'Linux 基础教程', 1000, 2, '2019-06-02 08:13:42' ),
	( 6, 'Python3 基础教程', 981, 3, '2019-03-18 13:52:03' ),
	( 7, 'JavaScript 基础教程', 73, 1, '2019-03-18 16:03:32' ),
	( 8, 'Ruby 基础教程', 199, 2, '2019-04-01 06:16:14' ),
	( 9, 'SQL 基础教程', 533, 3, '2019-05-02 08:13:42' ),
	( 10, 'Linux 基础教程', 1000, 1, '2019-06-02 08:13:42' );

基本使用

1. 统计各个教程的总访问量

   select name, sum(views) from lesson_views group by name;
   

   运行结果输出如下

   mysql> select name, sum(views) from lesson_views group by name;
   +---------------------+------------+
   | name                | sum(views) |
   +---------------------+------------+
   | Python3 基础教程    |       1962 |
   | JavaScript 基础教程 |        146 |
   | Ruby 基础教程       |        398 |
   | SQL 基础教程        |       1066 |
   | Linux 基础教程      |       2000 |
   +---------------------+------------+
   5 rows in set (0.00 sec)
   

SQL group by 多表连接

示例数据

DROP TABLE if exists lesson;

CREATE TABLE lesson (
    id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(32) default '',
    views int(11) NOT NULL default 0,
    created_at TIMESTAMP
);

INSERT INTO lesson(id,name,views,created_at) VALUES
	( 1, 'Python3 基础教程', 981, '2019-03-18 13:52:03' ),
	( 2, 'JavaScript 基础教程', 73, '2019-03-18 16:03:32' ),
	( 3, 'Ruby 基础教程', 199, '2019-04-01 06:16:14' ),
	( 4, 'SQL 基础教程', 533, '2019-05-02 08:13:42' ),
	( 5, 'Linux 基础教程', 1000, '2019-06-02 08:13:42' );

然后我们就可以使用下面的 SQL 语句查看每门课的 总访问量

select lesson.name,sum(lesson_views.views) from lesson,lesson_views where lesson.id = lesson_views.lesson_id group by lesson.name;

运行结果如下

mysql> select lesson.name, sum(lesson_views.views) from lesson, lesson_views whe
re lesson.id = lesson_views.lession_id group by lesson.name;
+---------------------+-------------------------+
| name                | sum(lesson_views.views) |
+---------------------+-------------------------+
| Python3 基础教程    |                    2587 |
| JavaScript 基础教程 |                    1272 |
| Ruby 基础教程       |                    1713 |
+---------------------+-------------------------+
3 rows in set (0.00 sec)