如何仅返回过去一年的MAX(date)？未发货物品SQL查询方案咨询

1. 先修正原查询的分组错误 #

你原查询的GROUP BY包含了t.created_on，这会导致每条交易日期单独成组，MAX(t.created_on)失去聚合统计的意义。正确的分组逻辑应该是按部件编号和交易类型分组，修正后的基础查询如下：

SELECT 
    i.part                    AS "Part Number",
    COUNT(DISTINCT i.item_id) AS "Quantity",
    t.transaction_code        AS "Type of Issue",
    MAX(t.created_on)         AS "Last Issue Date"
FROM
    Table1 i
JOIN Table2 t ON t.part = i.part
WHERE
    t.transaction_code IN ('XX1', 'XX2', 'XX3', 'XX4', 'XX5')
    AND i.segregation_code NOT IN ('YY1', 'YY2', 'YY3', 'YY4')
GROUP BY
    i.part,
    t.transaction_code
ORDER BY
    "Last Issue Date" DESC,
    i.part;

2. 过滤仅过去12个月内的最后交易日期 #

由于WHERE子句无法直接使用聚合函数，有两种可靠的实现方式：

方案一：使用HAVING子句过滤聚合结果 #

HAVING是专门用于筛选聚合后结果的子句，可以直接对MAX(t.created_on)进行判断：

SELECT 
    i.part                    AS "Part Number",
    COUNT(DISTINCT i.item_id) AS "Quantity",
    t.transaction_code        AS "Type of Issue",
    MAX(t.created_on)         AS "Last Issue Date"
FROM
    Table1 i
JOIN Table2 t ON t.part = i.part
WHERE
    t.transaction_code IN ('XX1', 'XX2', 'XX3', 'XX4', 'XX5')
    AND i.segregation_code NOT IN ('YY1', 'YY2', 'YY3', 'YY4')
GROUP BY
    i.part,
    t.transaction_code
HAVING
    MAX(t.created_on) >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY
    "Last Issue Date" DESC,
    i.part;

方案二：用CTE/子查询先聚合再过滤 #

如果需要更灵活的逻辑扩展，或者某些数据库对HAVING的支持有限，可以先通过CTE计算聚合结果，再做筛选：

WITH AggregatedParts AS (
    SELECT 
        i.part                    AS "Part Number",
        COUNT(DISTINCT i.item_id) AS "Quantity",
        t.transaction_code        AS "Type of Issue",
        MAX(t.created_on)         AS "Last Issue Date"
    FROM
        Table1 i
    JOIN Table2 t ON t.part = i.part
    WHERE
        t.transaction_code IN ('XX1', 'XX2', 'XX3', 'XX4', 'XX5')
        AND i.segregation_code NOT IN ('YY1', 'YY2', 'YY3', 'YY4')
    GROUP BY
        i.part,
        t.transaction_code
)
SELECT *
FROM AggregatedParts
WHERE "Last Issue Date" >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY
    "Last Issue Date" DESC,
    "Part Number";

3. 补充：如果需求是「过去12个月内从未发货的物品」 #

如果你的核心需求是找出过去12个月里完全没有产生XX1-XX5交易记录的未发货物品，需要改用左连接+空值判断的逻辑：

SELECT 
    i.part                    AS "Part Number",
    COUNT(DISTINCT i.item_id) AS "Quantity"
FROM
    Table1 i
LEFT JOIN Table2 t 
    ON t.part = i.part
    AND t.transaction_code IN ('XX1', 'XX2', 'XX3', 'XX4', 'XX5')
    AND t.created_on >= CURRENT_DATE - INTERVAL '12 months'
WHERE
    i.segregation_code NOT IN ('YY1', 'YY2', 'YY3', 'YY4')
    AND t.part IS NULL
GROUP BY
    i.part
ORDER BY
    i.part;

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