如何仅在一行中使用分组依据？

debugcn 发表于 Dev

阿黛布·马克

如果问题标题错误，请纠正我，

我在MySQL命名表 sales

-------------------------------------------------------------------------------------
| PRODUCT_NAME | PRODUCT_QUANTITY | PRODUCT_TYPE | In_Box_Count_Sell | ExpiryDate |
-------------------------------------------------------------------------------------
|     kkkkk    |       4          |   Count      |         1         |   2021-02-28 |
|     kkkkk    |       4          |   Count      |         1         |   2021-02-21 |
|     yuyuyu   |       3          |   Count      |         5         |   2021-02-21 |
-------------------------------------------------------------------------------------

另一个表名为 depot

    --------------------------------
    | ITEM_NAME | PRODUCT_QUANTITY | 
    --------------------------------
    |   kkkkk   |      12          |
    |   yuyuyu  |      15          |
    --------------------------------

我已经使用了这个查询：

SELECT `ExpiryDate`,`PRODUCT_NAME` As pdname,sales.`PRODUCT_QUANTITY`,`In_Box_Count_Sell`,`PRODUCT_TYPE`,(`depot`.`PRODUCT_QUANTITY`) As InDepotQuent FROM ((`sales`
INNER JOIN depot ON sales.PRODUCT_NAME = depot.ITEM_NAME)
INNER JOIN bill_info ON sales.BILL_NUMBER = bill_info.BILL_NUMBER)
WHERE bill_info.BILL_TYPE = 'مشتريات' 
ORDER BY `sales`.`PRODUCT_NAME`  ASC

其结果是：

---------------------------------------------------------------------------------------------
|  ExpiryDate | pdname | PRODUCT_QUANTITY | In_Box_Count_Sell | PRODUCT_TYPE | InDepotQuent |
---------------------------------------------------------------------------------------------
|  2021-02-21 | kkkkk  |       10         |        1          |     Count    |      12      |
|  2021-02-28 | kkkkk  |       4          |        1          |     Count    |      12      |
|  2021-08-25 | yuyuy  |       3          |        5          |     Count    |      15      |
---------------------------------------------------------------------------------------------

我需要得到的是atc=的总和PRODUCT_QUANTITY-的总和，InDepotQuent 结果将像这样：

---------------------------------------------------------------------------------------------------
|  ExpiryDate | pdname | PRODUCT_QUANTITY | In_Box_Count_Sell | PRODUCT_TYPE | InDepotQuent | atc |
---------------------------------------------------------------------------------------------------
|  2021-02-21 | kkkkk  |       10         |        1          |     Count    |      12      |  2  |
|  2021-02-28 | kkkkk  |       4          |        1          |     Count    |      12      |  2  |
|  2021-08-25 | yuyuy  |       3          |        5          |     Count    |      15      |  12 |
---------------------------------------------------------------------------------------------------

所以 sum of PRODUCT_QUANTITY = (14) - sum of InDepotQuent = (12)

的 atc = 2

并且考虑到pdname的名称在表中相似！

戈登·利诺夫

如果我理解正确，则可以使用窗口函数：

SELECT ExpierDate, PRODUCT_NAME As pdname, s.PRODUCT_QUANTITY, 
       In_Box_Count_Sell, PRODUCT_TIPE, d.PRODUCT_QUANTITY As InDepotQuent,
       SUM(s.PRODUCT_QUANTITY) OVER (PARTITION BY PRODUCT_NAME) - d.PRODUCT_QUANTITY as atc
FROM sales s JOIN
     depot d
     ON s.PRODUCT_NAME = d.ITEM_NAME JOIN
     bill_info bi
     ON s.BILL_NUMBER = bi.BILL_NUMBER
WHERE bi.BILL_TIPE = 'مشتريات' 
ORDER BY s.PRODUCT_NAME ASC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。