时间戳

首页 > 解决方案 > 如何使用 MySQL 获得取决于 id 的价格

mysql - 如何使用 MySQL 获得取决于 id 的价格

问题描述

price我将和 SUM相乘quantity得到总数。当我将不同表中的两列相乘时，我得到了错误的结果。sup_med_id价格与它刚刚占据的第一行不匹配10。我怎样才能得到价格取决于sup_med_id？

sup_med_id  price  sup_id      rec_order_dtls  sup_med_id  rec_quantity
1           10.00     2           1               1           200
2           12.00     2           2               2           100

date_id   date
1         2019-01-01
2         2019-01-02
...       ...

输出

Month  Quantity  Total  Supplier
...
Jul   0          0      NULL
Aug   300        3000   Unilever

我没有把所有的数据和表格放在上面。我希望这是下面的结果。

预期产出

Month  Quantity  Total  Supplier
...
Jul   0          0      NULL
Aug   300        3200   Unilever 

 SELECT DATE_FORMAT(tbl_date.date, '%b') 
 AS Month, 
  COUNT(tbl_purchase_received_details.purchase_received_id) 
 AS Total_Order, 
  SUM(IFNULL(tbl_purchase_received_details.received_quantity,0)) 
 AS Quantity, 
  IFNULL((tbl_supplier_medicine.price) * 
  SUM(tbl_purchase_received_details.received_quantity),0) 
 AS Total_Amount, 
  tbl_supplier.supplier_name 
 AS Supplier FROM tbl_date 
 LEFT JOIN 
  tbl_purchase_received ON tbl_purchase_received.date_received = tbl_date.date 
 LEFT JOIN 
  tbl_purchase_received_details ON tbl_purchase_received.purchase_received_id 
  = tbl_purchase_received_details.purchase_received_id 
 LEFT JOIN 
  tbl_supplier_medicine ON tbl_supplier_medicine.supplier_medicine_id = 
  tbl_purchase_received_details.supplier_medicine_id 
 LEFT JOIN tbl_supplier ON tbl_supplier.supplier_id = 
  tbl_supplier_medicine.supplier_id 
 WHERE YEAR(tbl_date.date) = YEAR(NOW()) 
 GROUP BY DATE_FORMAT(tbl_date.date, '%b')
 ORDER BY tbl_date.date

标签： mysqlsql

解决方案

您好，如果我正确理解您的问题，那么您需要将数量和价格相乘才能得到总数。如果这是正确的，那么您可以使用以下查询：

select tt.rec_quantity as quantity, (price * rec_quantity) as total from temp t inner join temp2 tt on t.sup_med_id=tt.sup_med_id;

如果其中一张表没有匹配数据，则可以使用left join。如果您需要帮助，请告诉我。

推荐阅读