数据结构
网络
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 次浏览
广告