mysql从同一行不同的列中选择上次输入日期和值

zxlwwiss  于 2021-06-15  发布在  Mysql
关注(0)|答案(2)|浏览(641)

基本上是试图从每个项目代码中获取最后的接收日期和购买的数量,但没有成功。下面是我从表中得到的

+----------+-------------+-------------------+
| ItemCode | ReceiptDate | QuantityPurchased |
+----------+-------------+-------------------+
| 107082   | 2018-10-15  |              1200 |
| 107082   | 2018-07-11  |               360 |
| 107082   | 2018-06-18  |              2760 |
| 107082   | 2018-05-30  |              1200 |
| 05505501 | 2018-05-11  |               576 |
| 107082   | 2018-05-11  |              1056 |
| 05505501 | 2018-04-20  |               600 |
| 107082   | 2018-04-20  |               600 |
| 05505501 | 2018-04-06  |               384 |
| 107082   | 2018-04-06  |               600 |
| 05505501 | 2018-02-19  |              1200 |
| 05505501 | 2018-02-06  |              1200 |
| 107082   | 2018-02-06  |               600 |
| 05505501 | 2018-02-02  |               600 |
| 107082   | 2018-02-02  |               600 |
| 05505501 | 2018-01-12  |              1200 |
+----------+-------------+-------------------+

这就是我想要的结果

+----------+-------------------------------+-------------+------------+---------------------+---------------+-----------------+------------------+-------------------+
| ItemCode | ItemCodeDesc                  | LastDatePur | LastQtyPur | TotalQuantityOnHand | Qty_0_90_Days | Qty_91_180_Days | Qty_181_270_Days | Qty_Over_270_Days |
+----------+-------------------------------+-------------+------------+---------------------+---------------+-----------------+------------------+-------------------+
| 05505501 | MDSBS-1-0 FANTA SALAD BWL CLR | 2018-05-11  |        576 |                3870 |             0 |               0 |              576 |                 0 |
| 107082   | MECRTL-6-00-6pc Round Acrylic | 2018-10-15  |       1200 |                3038 |          1200 |               0 |                0 |                 0 |
+----------+-------------------------------+-------------+------------+---------------------+---------------+-----------------+------------------+-------------------+

我所尝试的

SELECT DISTINCT ph.ItemCode, ph.ItemCodeDesc, MD.LastDatePur, MQ.LastQtyPur,
        i.TotalQuantityOnHand, 

        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 0 AND 90 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_0_90_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 91 AND 180 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_91_180_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 181 AND 270 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_181_270_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) > 270 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_Over_270_Days

FROM po_purchaseshistory ph, ci_item i, 
(SELECT ph.ItemCode, MAX(ph.ReceiptDate) AS LastDatePur FROM po_purchaseshistory ph WHERE ph.QuantityPurchased != 0 GROUP BY ph.ItemCode) AS MD,
(SELECT ph.ItemCode, ph.QuantityPurchased AS LastQtyPur FROM po_purchaseshistory ph WHERE ph.QuantityPurchased != 0 ORDER BY ph.ReceiptDate DESC LIMIT 1) AS MQ

WHERE ph.ItemCode = i.ItemCode
AND ph.ItemCode = MD.ItemCode
AND ph.ItemCode = MQ.ItemCode
AND ph.ItemCode IN (107082, 05505501)

我知道下面这行就是问题所在。尝试了子查询和分组,但都以错误告终。任何帮助都将不胜感激。

(SELECT ph.ItemCode, ph.QuantityPurchased AS LastQtyPur FROM po_purchaseshistory ph WHERE ph.QuantityPurchased != 0 ORDER BY ph.ReceiptDate DESC LIMIT 1) AS MQ
wydwbb8l

wydwbb8l1#

请在下面试用:

SELECT P.* FROM 
(
SELECT ItemCode,MAX(ReceiptDate) AS LastDate FROM po_purchaseshistory GROUP BY ItemCode
)A,po_purchaseshistory P WHERE P.ItemCode = A.ItemCode AND A.LastDate = P.ReceiptDate
au9on6nz

au9on6nz2#

以及有效的代码。。

SELECT DISTINCT ph.ItemCode, ph.ItemCodeDesc, MD.LastDatePur, MQ.LastQtyPur,
        i.TotalQuantityOnHand, 

        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 0 AND 90 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_0_90_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 91 AND 180 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_91_180_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) BETWEEN 181 AND 270 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_181_270_Days,
        (CASE WHEN DATEDIFF(CURDATE(), MD.LastDatePur) > 270 THEN MQ.LastQtyPur ELSE 0 END) AS Qty_Over_270_Days

FROM po_purchaseshistory ph 
LEFT JOIN ci_item i ON ph.ItemCode = i.ItemCode
LEFT JOIN (SELECT ph.ItemCode, MAX(ph.ReceiptDate) AS LastDatePur FROM po_purchaseshistory ph WHERE ph.QuantityPurchased != 0 GROUP BY ph.ItemCode) AS MD ON ph.ItemCode = MD.ItemCode
LEFT JOIN (SELECT ph.ItemCode, ph.ReceiptDate, ph.QuantityPurchased AS LastQtyPur FROM po_purchaseshistory ph WHERE ph.QuantityPurchased != 0) AS MQ ON ph.ItemCode = MQ.ItemCode

WHERE ph.ItemCode IN (107082, 05505501)
AND ph.ReceiptDate = MD.LastDatePur
AND ph.ReceiptDate = MQ.ReceiptDate

相关问题