PostgreSQL如何查询以表内最大订单日期为终点的近12个月订单

你可以用以下两种常用写法实现需求：

写法1：标量子查询（最简洁） #

直接将最大日期的计算作为标量值引用，不需要定义别名，语法兼容所有PostgreSQL版本：

SELECT * 
FROM orders 
WHERE order_date >= (SELECT MAX(order_date) FROM orders) - INTERVAL '1 year'
  AND order_date <= (SELECT MAX(order_date) FROM orders);

写法2：CTE预计算最大日期（性能更优，仅计算一次最大日期） #

如果表数据量较大，推荐用CTE仅计算一次最大日期，避免重复执行子查询：

WITH order_max_date AS (
    SELECT MAX(order_date) AS max_val FROM orders
)
SELECT o.* 
FROM orders o, order_max_date m
WHERE o.order_date >= m.max_val - INTERVAL '1 year'
  AND o.order_date <= m.max_val;

注意：不要用BETWEEN 大日期 AND 小日期的写法，BETWEEN要求左侧边界值小于等于右侧边界值，反向写法会返回空结果。

• 第一个报错：别名max_order_date不存在
  SQL的执行顺序远早于别名生成的阶段：SQL的执行顺序为 FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY，别名是在SELECT阶段才生成的，且你定义的max_order_date是给子查询的表取的别名，不是给MAX(order_date)的计算结果取的别名，子查询的作用域仅在子查询内部生效，外层查询无法直接引用，所以会提示别名不存在。

• 第二个报错：聚合函数不允许在WHERE子句中使用
  聚合函数（MAX、MIN、COUNT、SUM等）的计算依赖于分组后的数据集结果，而WHERE子句是在分组聚合之前执行的，作用是过滤原始表的行数据，此时聚合函数还没有完成计算，因此无法在WHERE子句中直接使用聚合函数。如果需要过滤聚合结果，需要使用HAVING子句，或者先通过子查询/CTE预计算聚合结果后再过滤。

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