如何将列相乘，然后将具有类似记录(例如客户名称)的行相加?
mysqlmysqli database
为了理解这一点，让我们创建一个包含 ID、客户名称、商品、价格等字段的表。我们首先将商品与价格相乘。之后，将添加具有类似记录(即相同的客户名称)的行。
首先我们创建一个表:
mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100), CustomerItems int, CustomerPrice int ); Query OK, 0 rows affected (0.54 sec)
下面是使用 insert 命令在表中插入一些记录的查询:
mysql> insert into DemoTable(CustomerName,CustomerItems,CustomerPrice)values('Larry',3,450);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable(CustomerName,CustomerItems,CustomerPrice)values('Mike',2,550);
Query OK, 1 row affected (0.42 sec)
mysql> insert into DemoTable(CustomerName,CustomerItems,CustomerPrice)values('Larry',4,1000);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(CustomerName,CustomerItems,CustomerPrice)values('Larry',1,100);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(CustomerName,CustomerItems,CustomerPrice)values('Mike',5,200);
Query OK, 1 row affected (0.71 sec)
以下是使用 select 命令显示表中记录的查询:
mysql> select *from DemoTable;
这将产生以下输出 −
+------------+--------------+---------------+---------------+ | CustomerId | CustomerName | CustomerItems | CustomerPrice | +------------+--------------+---------------+---------------+ | 1 | Larry | 3 | 450 | | 2 | Mike | 2 | 550 | | 3 | Larry | 4 | 1000 | | 4 | Larry | 1 | 100 | | 5 | Mike | 5 | 200 | +------------+--------------+---------------+---------------+ 5 rows in set (0.00 sec)
以下查询用于将列 (Items * Price) 相乘，然后对具有相似记录的行求和:
mysql> SELECT CustomerName, SUM( CustomerItems* CustomerPrice) AS TOTAL_PRICE FROM DemoTable GROUP BY CustomerName;
这将产生以下输出 −
+--------------+-------------+ | CustomerName | TOTAL_PRICE | +--------------+-------------+ | Larry | 5450 | | Mike | 2100 | +--------------+-------------+ 2 rows in set (0.05 sec)
