mysql - 如何在 1 Mysql 中进行求和、乘法和除法

Question

这个问题困扰我有一段时间了

表格:库存

|   customerID   | Item  |  Quantity  |  Price  |
|     cus1       |  A    |     3      |    4    |
|     cus1       |  B    |     2      |    3    |
|     cus1       |  C    |     3      |    3    |
|     cus2       |  A    |     3      |    3.50 |
|     cus2       |  C    |     3      |    2    |
|     cus3       |  D    |     6      |    4    |

如何让我的 mysql 查询生成如下所示的 View

查看:stock_v

| cusID  | Item |Quan |Price |Tot_Qua | Tot_pri | sumtot_pri/tot_qua|
|  cus1  |  A   |  3  | 4    |   8    | 12      | 3.375             | 
|  cus1  |  B   |  2  | 3    |   8    | 6       | 3.375             | 
|  cus1  |  C   |  3  | 3    |   8    | 9       | 3.375             |
|  cus2  |  A   |  3  | 3.50 |   6    | 10.05   | 2.675             |
|  cus2  |  C   |  3  | 2    |   6    | 6       | 2.675             | 
|  cus3  |  D   |  6  | 4    |   6    | 24      | 4.00              |

cus1 的示例。Cus1 有 3 项，即 A、B 和 C。所以我想要一些公式

Tot_Qua = 3+2+3 = 8
Tot_pri = price x quan

并且 Tot_pri 必须为此 cus1 记录求和，

sumtot_pri = (quan x price) + (quan x price) + (quan x price)
sumtot_pri = (12) + (6) + (9)

因为 cus1 有 3 项，最后一项

sumtot_pri / Tot_qua = 27 / 8 = 3.375

我认为需要分组，因为我想查看他们的项目。我不在乎列 tot_qua 和列 sumtot_pri/tot_qua 是否会为每个 Cus 重复相同的数据。

Answer 1

在 MySQL 8+ 中，您只需使用窗口函数:

select s.*,
       sum(quan) over (partition by cusid) as tot_quan,
       (quan * price) as tot_price,
       sum(quan * price) over (partition by cusid) / sum(quan) over (partition by cusid) as ratio
from stock s;

在早期版本中，您将使用子查询或类似的机制。可能 join 和 group by 最简单:

select s.*, s2.tot_quan, (s.quan * s.price) as tot_price,
       tot_tot_price / tot_quan as ratio
from stock s join
     (select cusid, sum(quan) as tot_quan,
            sum(quan * price) as tot_tot_price
      from stock s2
      group by cusid
     ) s2
     using (cusid);