mysql - 表中账单金额的总和，并将具有相同账单的重复行视为相同

Question

在我的表中，我存储供应商的采购 list 。喜欢:

| Bill No | Vendor | Item | Bill Amount |
| 1929    | abc    | 123  |   840.00    | <-- Treat these as one
| 1929    | abc    | 234  |   840.00    | <-- Treat these as one
| 23874   | xyz    | peo  |  1024.00    |
| 1942    | abc    | pop  |   600.00    |
| 4720    | mno    | test |  1250.00    |

我想要获取供应商的总金额组，其中 mysql 将相同的账单编号视为 1 条记录。输出应该是。

| Vendor | Total Amount |
| abc    |    1440.00   | <-- It should be 1440 instead of 2280
| xyz    |    1024.00   |
| mno    |    1250.00   |

Answer 1

一种方法是在派生表中获取不同的账单编号及其值，然后对这些值使用求和并进行分组。

考虑下表

mysql> select * from bills ;
+--------+--------+------+-------------+
| billno | vendor | item | bill_amount |
+--------+--------+------+-------------+
|   1929 | abc    | 123  |      840.00 |
|   1929 | abc    | 234  |      840.00 |
|  23874 | xyz    | peo  |     1024.00 |
|   1942 | abc    | pop  |      600.00 |
|   4720 | mno    | test |     1250.00 |
+--------+--------+------+-------------+
5 rows in set (0.00 sec)

然后通过下面的查询我们可以获得数据

select 
sum(x.bill_amount) as total_amount,
x.vendor from (
   select distinct billno, vendor , bill_amount from bills 
)x 
group by x.vendor ;


+--------------+--------+
| total_amount | vendor |
+--------------+--------+
|      1440.00 | abc    |
|      1250.00 | mno    |
|      1024.00 | xyz    |
+--------------+--------+
3 rows in set (0.00 sec)