mysql - 在 WHERE 子句中选择日期间隔

Question

我正在尝试查询过去 36 个月内开设的贷款列表。我还尝试将过去 12 个月内开立的贷款计数作为单独的列进行查询。

查询为两列返回相同的值，如何在不运行单独查询的情况下获取 36 个月和 12 个月间隔的计数？

SELECT 
`XDL-NAME`, 
COUNT(distinct`XLN-LOANDATE`) as '36 Month Count',
COUNT(IF(`XLN-LOANDATE` >= DATE_SUB(NOW(),INTERVAL 12 MONTH), 1 , NULL)) AS '12 Month Count' 
 from
 LOAN
 JOIN
 DEALER ON `XLN-DLNO` = `XDL-NUM` WHERE `XLN-LOANDATE` >= DATE_SUB(NOW(),INTERVAL 36 MONTH)

 GROUP BY `XDL-NAME`

Answer 1

将 count 改为 12 个月，改为 sum，如下所示

SELECT 
`XDL-NAME`, 
COUNT(distinct`XLN-LOANDATE`) as '36 Month Count',
SUM(IF(`XLN-LOANDATE` >= DATE_SUB(NOW(),INTERVAL 12 MONTH), 1 , 0)) AS '12 Month Count' 
 from
 LOAN
 JOIN
 DEALER ON `XLN-DLNO` = `XDL-NUM` WHERE `XLN-LOANDATE` >= DATE_SUB(NOW(),INTERVAL 36 MONTH)

 GROUP BY `XDL-NAME`