使用日期差异和 sumif 进行老化分析。我没有获得正确的老化数据。请检查我的查询是否正常

Question

SELECT t1.CurrencyCode, t1.PartersCode, t2.Name,t1.GuaranteeNoXOL,t1.GuaranteeNo,t1.GuaranteeNo_Grp,t1.TotalAmountExpected,t1.statementdate,
DATEDIFF(CURDATE(), t1.statementdate) AS days_past_due,
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) = 0, t1.TotalAmountExpected, 0)) AS 'Today',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1 AND 30, t1.TotalAmountExpected, 0)) AS '1 - 30 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 31 AND 60, t1.TotalAmountExpected, 0)) AS '31 - 60 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 61 AND 90, t1.TotalAmountExpected, 0)) AS '61 - 90 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 91 AND 120, t1.TotalAmountExpected, 0)) AS '91 - 120 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 121 AND 180, t1.TotalAmountExpected, 0)) AS '121 - 180 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 181 AND 360, t1.TotalAmountExpected, 0)) AS '181 - 360 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 361 AND 720, t1.TotalAmountExpected, 0)) AS '1 - 2 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 721 AND 1080, t1.TotalAmountExpected, 0)) AS '2 - 3 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1081 AND 1440, t1.TotalAmountExpected, 0)) AS '3 - 4 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1441 AND 1800, t1.TotalAmountExpected, 0)) AS '4 - 5 Years', 
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1081 AND 2160, t1.TotalAmountExpected, 0)) AS '5 - 6 Years', 
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) > 2160, t1.TotalAmountExpected, 0)) AS 'Over 6 Years' FROM debtorsregisterinfo t1
INNER JOIN partnersinfo t2 ON t2.Id = t1.PartersCode
WHERE  t1.fullypaid=0 AND t1.exclude=0 AND t1.reversed=0
GROUP BY t1.PartersCode, t1.CurrencyCode ORDER BY  t2.Name ASC, t1.statementdate DESC;

Answer 1

我看到你在评论中提到了你的"期望"。与"actual"Gap正在被另一个查询验证。

如果是这样的话，你很可能对mysql如何执行group by语句产生了一个非常普遍的误解。

在mysql config中有一个配置设置强制执行"strict"分组规则。这些最近正成为默认策略。

在分组时，如果您的选择列在group by语句中不是全部存在，那么您的意图可能无法正确返回。这是因为其他列可能是soft grouped，在这种情况下，mysql引擎将您的选择列转换为any_value('mycol')。any_value('mycol')将返回任意的根据您的筛选器语句从列中获取行值。这意味着检索到的结果集可能不能保证与同一查询在不同时间的另一次执行完全匹配。

更糟的是，结果可能偶然返回您期望的结果集，并且您可能在错误的假设下继续执行查询。

为了防止这种情况，要么更改mysql配置标志以强制执行严格的分组规则，要么在group by语句中始终包含select语句中的每个non-aggregate列。

也就是说:

SELECT t1.CurrencyCode, t1.PartersCode, t2.Name,t1.GuaranteeNoXOL,t1.GuaranteeNo,t1.GuaranteeNo_Grp,t1.TotalAmountExpected,t1.statementdate,
DATEDIFF(CURDATE(), t1.statementdate) AS days_past_due,
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) = 0, t1.TotalAmountExpected, 0)) AS 'Today',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1 AND 30, t1.TotalAmountExpected, 0)) AS '1 - 30 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 31 AND 60, t1.TotalAmountExpected, 0)) AS '31 - 60 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 61 AND 90, t1.TotalAmountExpected, 0)) AS '61 - 90 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 91 AND 120, t1.TotalAmountExpected, 0)) AS '91 - 120 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 121 AND 180, t1.TotalAmountExpected, 0)) AS '121 - 180 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 181 AND 360, t1.TotalAmountExpected, 0)) AS '181 - 360 Days',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 361 AND 720, t1.TotalAmountExpected, 0)) AS '1 - 2 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 721 AND 1080, t1.TotalAmountExpected, 0)) AS '2 - 3 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1081 AND 1440, t1.TotalAmountExpected, 0)) AS '3 - 4 Years',
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1441 AND 1800, t1.TotalAmountExpected, 0)) AS '4 - 5 Years', 
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1081 AND 2160, t1.TotalAmountExpected, 0)) AS '5 - 6 Years', 
SUM(IF(DATEDIFF(CURDATE(), t1.statementdate) > 2160, t1.TotalAmountExpected, 0)) AS 'Over 6 Years' FROM debtorsregisterinfo t1
INNER JOIN partnersinfo t2 ON t2.Id = t1.PartersCode
WHERE  t1.fullypaid=0 AND t1.exclude=0 AND t1.reversed=0
GROUP BY t1.PartersCode,t1.CurrencyCode,t2.Name,t1.GuaranteeNoXOL,t1.GuaranteeNo,t1.GuaranteeNo_Grp,t1.TotalAmountExpected,t1.statementdate ORDER BY t2.Name ASC, t1.statementdate DESC;

应该工作

Answer 2

SELECT t1.idkey , t1.CurrencyCode, t1.PartersCode, t2.Name,t1.GuaranteeNoXOL,t1.GuaranteeNo,t1.GuaranteeNo_Grp,t1.TotalAmountExpected,t1.statementdate,
DATEDIFF(CURDATE(), t1.statementdate) AS days_past_due,
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) = 0 THEN t1.TotalAmountExpected ELSE 0 END) AS 'Today',
SUM(CASE
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 1 AND DATEDIFF(CURDATE(), t1.statementdate) <= 30 THEN t1.TotalAmountExpected ELSE 0 END) AS '1 - 30 Days',
SUM(CASE
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 31 AND DATEDIFF(CURDATE(), t1.statementdate) <= 60 THEN t1.TotalAmountExpected ELSE 0 END) AS '31 - 60 Days',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 61 AND DATEDIFF(CURDATE(), t1.statementdate) <= 90 THEN t1.TotalAmountExpected ELSE 0 END) AS '61 - 90 Days',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 91 AND DATEDIFF(CURDATE(), t1.statementdate) <= 120 THEN t1.TotalAmountExpected ELSE 0 END) AS '91 - 120 Days',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 121 AND DATEDIFF(CURDATE(), t1.statementdate) <= 180 THEN t1.TotalAmountExpected ELSE 0 END) AS '121 - 180 Days',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 181 AND DATEDIFF(CURDATE(), t1.statementdate) <= 360 THEN t1.TotalAmountExpected ELSE 0 END) AS '181 - 360 Days',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 361 AND DATEDIFF(CURDATE(), t1.statementdate) <= 720 THEN t1.TotalAmountExpected ELSE 0 END) AS '1 - 2 Years',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 721 AND DATEDIFF(CURDATE(), t1.statementdate) <= 1080 THEN t1.TotalAmountExpected ELSE 0 END) AS '2 - 3 Years',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 1081 AND DATEDIFF(CURDATE(), t1.statementdate) <= 1440 THEN t1.TotalAmountExpected ELSE 0 END) AS '3 - 4 Years',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 1441 AND DATEDIFF(CURDATE(), t1.statementdate) <= 1800 THEN t1.TotalAmountExpected ELSE 0 END) AS '4 - 5 Years',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) >= 1801 AND DATEDIFF(CURDATE(), t1.statementdate) <= 2160 THEN t1.TotalAmountExpected ELSE 0 END) AS '5 - 6 Years',
SUM(CASE 
WHEN DATEDIFF(CURDATE(), t1.statementdate) > 2160 THEN t1.TotalAmountExpected ELSE 0 END) AS 'Over 6 Years'
FROM debtorsregisterinfo t1
LEFT JOIN partnersinfo t2 ON t1.PartersCode = t2.Id
WHERE t1.reversed = 0
AND t1.fullypaid = 0
AND t1.exclude = 0
GROUP BY t1.idkey, t1.PartersCode, t1.CurrencyCode, t2.Name, t1.GuaranteeNoXOL,t1.GuaranteeNo,t1.GuaranteeNo_Grp,t1.TotalAmountExpected,t1.statementdate,
DATEDIFF(CURDATE(), t1.statementdate) 
ORDER BY t1.IdKey, t2.Name ASC, t1.statementdate DESC;