資料結構
網路
RDBMS
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會科學
時尚研究
法律研究使用 MySQL 查詢選擇多個求和項並將其顯示在不同的列中？
要使用 MySQL 查詢選擇多個求和列並將其顯示在不同的列中，你需要使用 CASE 語句。語法如下
SELECT SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1, SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2, SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3, . . . N FROM yourTableName;
為了理解上述語法，我們建立一個表格。建立表格的查詢如下
mysql> create table selectMultipleSumDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PlayerName varchar(20), -> PlayerScore int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.58 sec)
現在你可以使用插入命令向表格中插入一些記錄。查詢如下
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',89);
Query OK, 1 row affected (0.23 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',98);
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',96);
Query OK, 1 row affected (0.18 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',78);
Query OK, 1 row affected (0.16 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Maxwell',51);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('Ricky',89);
Query OK, 1 row affected (0.21 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('David',56);
Query OK, 1 row affected (0.15 sec)
mysql> insert into selectMultipleSumDemo(PlayerName,PlayerScore) values('David',65);
Query OK, 1 row affected (0.19 sec)使用 select 語句從表格中顯示所有記錄。查詢如下
mysql> select *from selectMultipleSumDemo;
輸出如下
+----+------------+-------------+ | Id | PlayerName | PlayerScore | +----+------------+-------------+ | 1 | Maxwell | 89 | | 2 | Ricky | 98 | | 3 | Maxwell | 96 | | 4 | Ricky | 78 | | 5 | Maxwell | 51 | | 6 | Ricky | 89 | | 7 | David | 56 | | 8 | David | 65 | +----+------------+-------------+ 8 rows in set (0.00 sec)
獲取具有多個求和項的單獨列的查詢
mysql> select -> SUM(CASE WHEN PlayerName='Maxwell' THEN PlayerScore END) AS 'MAXWELL TOTAL SCORE', -> SUM(CASE WHEN PlayerName='Ricky' THEN PlayerScore END) AS 'RICKY TOTAL SCORE', -> SUM(CASE WHEN PlayerName='David' THEN PlayerScore END) AS 'DAVID TOTAL SCORE' -> from selectMultipleSumDemo;
輸出如下
+---------------------+-------------------+-------------------+ | MAXWELL TOTAL SCORE | RICKY TOTAL SCORE | DAVID TOTAL SCORE | +---------------------+-------------------+-------------------+ | 236 | 265 | 121 | +---------------------+-------------------+-------------------+ 1 row in set (0.00 sec)
更新於: 30-Jul-2019
897 次瀏覽
廣告