如何优雅编写PostgreSQL查询以获取发票的最小/最大日期及对应分店ID

需求回顾 #

查询发票数据中的最小日期和最大日期，并展示对应日期的分店ID；若同一最小/最大日期下有多个分店存在发票记录，可任选其一。

测试数据准备 #

CREATE TEMP TABLE invoice ( id int not null, branch_id int not null, c_date date not null, PRIMARY KEY (id) );
insert into invoice (id, branch_id, c_date) values 
(1, 1, '2020-01-01') ,
(2, 2, '2020-01-01') ,
(3, 1, '2020-01-02') ,
(4, 2, '2020-01-02') ,
(5, 2, '2020-01-03');

方案1：PostgreSQL专属极简写法（推荐） #

PostgreSQL的DISTINCT ON特性可以直接按指定字段去重，保留排序后的第一条记录，完美适配这种“取极值日期对应任意分店”的场景：

-- 一次性获取最小/最大日期及对应分店
SELECT
    -- 最小日期及分店
    (SELECT branch_id FROM invoice ORDER BY c_date ASC LIMIT 1) AS min_date_branch,
    (SELECT c_date FROM invoice ORDER BY c_date ASC LIMIT 1) AS min_date,
    -- 最大日期及分店
    (SELECT branch_id FROM invoice ORDER BY c_date DESC LIMIT 1) AS max_date_branch,
    (SELECT c_date FROM invoice ORDER BY c_date DESC LIMIT 1) AS max_date;

如果想指定同日期下优先选某个分店（比如branch_id更小的），可以在ORDER BY里追加排序条件：

(SELECT branch_id FROM invoice ORDER BY c_date ASC, branch_id ASC LIMIT 1) AS min_date_branch

方案2：跨数据库通用写法（兼容MySQL/SQL Server/PostgreSQL等） #

如果需要适配多种数据库，这个写法更稳妥——先通过子查询拿到极值日期，再关联获取对应分店（用LIMIT 1实现“任选其一”）：

SELECT
    (SELECT branch_id FROM invoice WHERE c_date = (SELECT MIN(c_date) FROM invoice) LIMIT 1) AS min_date_branch,
    (SELECT MIN(c_date) FROM invoice) AS min_date,
    (SELECT branch_id FROM invoice WHERE c_date = (SELECT MAX(c_date) FROM invoice) LIMIT 1) AS max_date_branch,
    (SELECT MAX(c_date) FROM invoice) AS max_date;

注：SQL Server需把LIMIT 1换成TOP 1；Oracle则用ROWNUM <= 1替代。

方案3：简化版窗口函数写法 #

你之前的窗口函数方案可以大幅简化，减少冗余嵌套层级，可读性更强：

WITH ranked_invoices AS (
    SELECT
        branch_id,
        c_date,
        RANK() OVER (ORDER BY c_date) AS rank_min,
        RANK() OVER (ORDER BY c_date DESC) AS rank_max
    FROM invoice
)
SELECT
    (SELECT branch_id FROM ranked_invoices WHERE rank_min = 1 LIMIT 1) AS min_date_branch,
    (SELECT c_date FROM ranked_invoices WHERE rank_min = 1 LIMIT 1) AS min_date,
    (SELECT branch_id FROM ranked_invoices WHERE rank_max = 1 LIMIT 1) AS max_date_branch,
    (SELECT c_date FROM ranked_invoices WHERE rank_max = 1 LIMIT 1) AS max_date;

方案优势对比 #

• 相比你原来的“直白写法”，这些方案去掉了冗余的表关联，逻辑更清晰；
• 相比你原来的窗口函数写法，简化了子查询嵌套，维护成本更低；
• 通用写法适配性广，PostgreSQL专属写法则是性能和简洁度最优的选择。

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