資料結構
網路
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+ 瀏覽量
廣告