我们如何使用GROUP BY子句创建MySQL视图？
我们可以使用GROUPBY对列中的值进行分组，并且，如果需要，我们可以对该列执行计算。您可以在分组列上使用COUNT，SUM，AVG等功能。为了理解带有视图的GROUPBY子句，我们使用具有以下数据的基表'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; 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;
上面的查询在GROUPBY子句中有两列。
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)
上面的结果集表明，GROUPBY子句通过列的值将一组行分组为一组汇总行。