MySQL账龄分析用DATEDIFF和SUMIF数据错误，请求查询语句校验

已发现的错误点 #

• GROUP BY 与SELECT字段不匹配：当前语句仅按t1.PartersCode、t1.CurrencyCode两个字段分组，但SELECT中包含了t1.GuaranteeNoXOL、t1.statementdate等多个非聚合、未加入分组规则的字段。MySQL 5.7及以上版本默认开启ONLY_FULL_GROUP_BY校验，会直接报错；关闭校验的场景下，MySQL会随机返回分组内某一行的非聚合字段值，直接导致账龄计算、金额汇总结果错误。
• 账龄区间重复统计：5 - 6 Years对应的区间写为BETWEEN 1081 AND 2160，和前面3 - 4 Years（1081-1440）、4 - 5 Years（1441-1800）的区间完全重叠，这部分到期的金额会被同时计入多个区间，导致统计结果偏大。
• 符号转义错误：语句中的>是HTML转义字符，直接在MySQL中执行会报错，需要替换为原生大于号>。

修正示例 #

场景1：按「合作方+币种」维度汇总账龄 #

SELECT 
  t1.CurrencyCode, 
  t1.PartersCode, 
  MAX(t2.Name) AS partner_name,
  SUM(t1.TotalAmountExpected) AS total_unpaid,
  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 1801 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 partner_name ASC;

场景2：按单笔单据维度展示账龄 #

不需要聚合和分组，直接计算每笔单据的逾期天数和对应区间即可：

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,
  IF(DATEDIFF(CURDATE(), t1.statementdate) = 0, t1.TotalAmountExpected, 0) AS 'Today',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1 AND 30, t1.TotalAmountExpected, 0) AS '1 - 30 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 31 AND 60, t1.TotalAmountExpected, 0) AS '31 - 60 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 61 AND 90, t1.TotalAmountExpected, 0) AS '61 - 90 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 91 AND 120, t1.TotalAmountExpected, 0) AS '91 - 120 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 121 AND 180, t1.TotalAmountExpected, 0) AS '121 - 180 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 181 AND 360, t1.TotalAmountExpected, 0) AS '181 - 360 Days',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 361 AND 720, t1.TotalAmountExpected, 0) AS '1 - 2 Years',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 721 AND 1080, t1.TotalAmountExpected, 0) AS '2 - 3 Years',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1081 AND 1440, t1.TotalAmountExpected, 0) AS '3 - 4 Years',
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1441 AND 1800, t1.TotalAmountExpected, 0) AS '4 - 5 Years', 
  IF(DATEDIFF(CURDATE(), t1.statementdate) BETWEEN 1801 AND 2160, t1.TotalAmountExpected, 0) AS '5 - 6 Years', 
  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
ORDER BY t2.Name ASC, t1.statementdate DESC;

内容的提问来源于stack exchange，提问作者Leslie