为此使用GROUP BY子句。让我们首先创建一个表-mysql> create table sumOfFieldsDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ClientSerialNumber varchar(100),
-> ClientCost int
-> );
以下是使用insert命令在表中插入一些记录的查询-mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('1111',450);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('2222',550);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('3333',150);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('3333',250);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('2222',1000);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('1111',1000);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('1111',500);
mysql> insert into sumOfFieldsDemo(ClientSerialNumber,ClientCost) values('4444',100);
以下是使用select语句显示表中所有记录的查询-mysql> select * from sumOfFieldsDemo;
这将产生以下输出-+----+--------------------+------------+
| Id | ClientSerialNumber | ClientCost |
+----+--------------------+------------+
| 1 | 1111 | 450 |
| 2 | 2222 | 550 |
| 3 | 3333 | 150 |
| 4 | 3333 | 250 |
| 5 | 2222 | 1000 |
| 6 | 1111 | 1000 |
| 7 | 1111 | 500 |
| 8 | 4444 | 100 |
+----+--------------------+------------+
8 rows in set (0.00 sec)
这是查询以查找具有相同列值的字段总和-mysql> select Id,ClientSerialNumber,SUM(ClientCost) AS TotalSum
-> from sumOfFieldsDemo
-> group by ClientSerialNumber;
这将产生以下输出-+----+--------------------+----------+
| Id | ClientSerialNumber | TotalSum |
+----+--------------------+----------+
| 1 | 1111 | 1950 |
| 2 | 2222 | 1550 |
| 3 | 3333 | 400 |
| 8 | 4444 | 100 |
+----+--------------------+----------+
4 rows in set (0.00 sec)