如何在HAVING子句中引用另一查询的n.Start与n.End列？

我已经写了一个生成日期范围的查询，用来得到过去13个月（含当月）的每个月起止日期：

SELECT n.order, n.Start, n.End
FROM 
(SELECT t.order
, LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
, LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
FROM 
(SELECT 0 as 'order' 
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
) n

查询结果如下：

现在我需要统计每个日期范围内，首次手术日期落在该区间的患者数量，目标查询逻辑是：

SELECT COUNT(b.PatNum) FROM
    (SELECT pl.PatNum
    FROM procedurelog pl 
    WHERE pl.ProcStatus=2
    GROUP BY pl.PatNum
    HAVING MIN(pl.ProcDate) BETWEEN n.Start AND n.End) b

但尝试时总是出现“having子句中存在未知列...”的错误，最接近的写法也只返回单行结果：

SELECT b.order, b.Start, b.End, COUNT(b.PatNum)
FROM 
(   SELECT n.order, n.Start, n.End,pl.PatNum
    FROM procedurelog pl
    ,(  SELECT t.order
        , LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
        , LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
        FROM 
        (SELECT 0 as 'order' 
        UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
    ) n
    GROUP BY pl.PatNum
    HAVING MIN(pl.ProcDate) BETWEEN n.Start AND n.End
) b

问题核心是关联逻辑和分组逻辑错误，正确写法是先计算患者首次手术日期，再和日期范围表做关联统计：

SELECT 
    n.order, 
    n.Start, 
    n.End, 
    COUNT(p.PatNum) AS PatientCount
FROM 
    (SELECT t.order
        , LAST_DAY(CURDATE() - INTERVAL (t.order+1) MONTH) + INTERVAL 1 DAY  AS 'Start'
        , LAST_DAY(CURDATE() - INTERVAL t.order MONTH) AS 'End'
        FROM 
        (SELECT 0 as 'order' 
        UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) t
    ) n
LEFT JOIN 
    (SELECT 
        PatNum, 
        MIN(ProcDate) AS FirstProcDate
     FROM procedurelog 
     WHERE ProcStatus=2
     GROUP BY PatNum) p 
ON p.FirstProcDate BETWEEN n.Start AND n.End
GROUP BY n.order, n.Start, n.End
ORDER BY n.order;

逻辑说明： #

1. 子查询p先筛选出ProcStatus=2的记录，按患者分组计算每个人的首次手术日期FirstProcDate。
2. 将日期范围表n和子查询p做左连接，关联条件为患者首次手术日期落在对应月份的起止区间内。
3. 按日期范围的order、Start、End分组统计，这样每个月份都会返回对应的患者数量，无符合条件患者的月份也会显示0，不会遗漏任何区间。

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