我试图将下表总结成一行,包括3个月、6个月、9个月等等,但是遇到了mysql错误。我查找了其他类似的帖子,并用完整的case将group替换为alias,但仍然说包含未聚合的列。任何帮助都将不胜感激。
mysql> SELECT ith.ItemCode, ith.TransactionDate, DATEDIFF(CURDATE(), ith.TransactionDate) AS Date_Diff,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 0_90_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 91_180_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 181_270_Days,
-> (CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over_270_Days
->
-> FROM im_itemtransactionhistory ith, ci_item i
-> WHERE ith.TransactionCode = 'PO'
-> AND ith.TransactionDate >= '2018-01-01'
-> AND ith.ItemCode = i.ItemCode
-> AND i.TotalQuantityOnHand > 0
-> AND ith.ItemCode = 512619
-> GROUP BY ith.ItemCode, ith.TransactionDate, ith.TransactionQty
->
-> ORDER BY ith.TransactionDate ASC;
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
| ItemCode | TransactionDate | Date_Diff | 0_90_Days | 91_180_Days | 181_270_Days | Over_270_Days |
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
| 512619 | 2018-03-19 | 220 | 0 | 0 | 1200 | 0 |
| 512619 | 2018-05-10 | 168 | 0 | 3600 | 0 | 0 |
| 512619 | 2018-05-11 | 167 | 0 | 600 | 0 | 0 |
| 512619 | 2018-06-15 | 132 | 0 | 3600 | 0 | 0 |
| 512619 | 2018-06-26 | 121 | 0 | 2400 | 0 | 0 |
| 512619 | 2018-07-23 | 94 | 0 | 2400 | 0 | 0 |
| 512619 | 2018-07-26 | 91 | 0 | 1200 | 0 | 0 |
| 512619 | 2018-09-10 | 45 | 1200 | 0 | 0 | 0 |
| 512619 | 2018-09-10 | 45 | 2400 | 0 | 0 | 0 |
| 512619 | 2018-10-16 | 9 | 1092 | 0 | 0 | 0 |
+----------+-----------------+-----------+-----------+-------------+--------------+---------------+
这就是我尝试过的。。
SELECT ith.ItemCode, ith.TransactionCode, i.TotalQuantityOnHand, DATEDIFF(CURDATE(), ith.TransactionDate),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 0_90_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 91_180_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 181_270_Days,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over_270_Days,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END) AS 3m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END) AS 6m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END) AS 9m_total,
SUM(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END) AS Over9m_total
FROM im_itemtransactionhistory ith, ci_item i
WHERE ith.TransactionCode = 'PO'
AND ith.TransactionDate >= '2018-01-01'
AND ith.ItemCode = i.ItemCode
AND i.TotalQuantityOnHand > 0
AND ith.ItemCode = 512619
GROUP BY ith.ItemCode,
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 0 AND 90 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 91 AND 180 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) BETWEEN 181 AND 270 THEN ith.TransactionQty ELSE 0 END),
(CASE WHEN DATEDIFF(CURDATE(), ith.TransactionDate) > 270 THEN ith.TransactionQty ELSE 0 END)
ORDER BY ith.TransactionDate ASC
错误代码将返回
# 1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mas90.ith.TransactionDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这就是我想要的结果。。
+----------+-----------+-------------+--------------+---------------+
| ItemCode | 0_90_Days | 91_180_Days | 181_270_Days | Over_270_Days |
+----------+-----------+-------------+--------------+---------------+
| 512619 | 4692 | 13800 | 1200 | 0 |
+----------+-----------+-------------+--------------+---------------+
1条答案
按热度按时间7rfyedvj1#
在罗伯特·科克的帮助下,我用下面的问题解决了这个问题。
并公布结果,希望这能帮助有同样问题的人。