我们可以使用GROUP BY对列中的值进行分组，并且如果需要，我们可以对该列执行计算。您可以在分组列上使用 COUNT、SUM、AVG 等函数。为了理解带有视图的 GROUP BY 子句，我们使用具有以下数据的基表“Student_info”创建一个名为“Info”的视图 -
mysql> Select * from Student_info;
+------+---------+------------+------------+
| id | Name | Address | Subject |
+------+---------+------------+------------+
| 101 | YashPal | Amritsar | History |
| 105 | Gaurav | Chandigarh | Literature |
| 125 | Raman | Shimla | Computers |
| 130 | Ram | Jhansi | Computers |
| 132 | Shyam | Chandigarh | Economics |
| 133 | Mohan | Delhi | Computers |
+------+---------+------------+------------+
6 rows in set (0.00 sec)
语法
Create or Replace View view_name AS Select_statements FROM table GROUP BY expression1, expression2, ... expression_n;
示例
mysql> Create or Replace View Info AS select Subject, COUNT(*) FROM Student_info GROUP BY Subject;
Query OK, 0 rows affected (0.10 sec)
mysql> Select * from info;
+------------+----------+
| Subject | COUNT(*) |
+------------+----------+
| Computers | 3 |
| Economics | 1 |
| History | 1 |
| Literature | 1 |
+------------+----------+
4 rows in set (0.00 sec)
mysql> Create or Replace View Info AS select Subject,Name, COUNT(Subject) FROM Student_info GROUP BY Subject, Name;
Query OK, 0 rows affected (0.05 sec)
上面的查询将在 GROUP BY 子句中包含两列。
mysql> Select * from info;
+------------+---------+----------------+
| Subject | Name | COUNT(Subject) |
+------------+---------+----------------+
| Computers | Mohan | 1 |
| Computers | Ram | 1 |
| Computers | Raman | 1 |
| Economics | Shyam | 1 |
| History | YashPal | 1 |
| Literature | Gaurav | 1 |
+------------+---------+----------------+
6 rows in set (0.00 sec)
上面的结果集显示，GROUP BY 子句按列的值将一组行分组为一组汇总行。
