MySQL查询找到具有相同列值的字段总和？
为此使用GROUPBY子句。让我们首先创建一个表-
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)