如何使用 GROUP BY 子句创建 MySQL 视图?
mysqlmysqli database
我们可以使用 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 子句根据列的值将一组行分组为一组汇总行。
