数据结构
网络
RDBMS
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化学
生物
数学
英语
经济
心理学
社会学
时尚研究
法律研究如何使用 GROUP BY 子句创建 MySQL 视图？
我们可以使用 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 子句根据列值将一组行分组为一组汇总行。
更新日期：30-Jul-2019
2K+ 浏览量
广告