PostgreSQL多表连接子查询取MAX值的SQL写法咨询

• 第二部分需求实现逻辑：将orderEntry表与product表通过product_id字段关联，按order_id分组后，直接对min_duration字段取最大值即可。PostgreSQL中interval类型的PxxD时间间隔格式原生支持大小比较，无需额外做类型转换。
• 两部分结果整合：可以将你已实现的「每位客户最新订单」查询作为子查询/CTE，和上述计算出的订单最大时长结果通过order_id字段关联，最后关联Customers表取出客户姓名，就能得到最终结果集。
• 额外优化提示：PostgreSQL提供的DISTINCT ON语法可以更高效地实现「取每个分组最新一条记录」的需求，比你当前用的MAX()子查询关联写法性能更好。

第二部分需求单独查询语句 #

SELECT
    oe.order_id,
    MAX(p.min_duration) AS max_min_duration
FROM "orderEntry" oe
INNER JOIN product p
    ON oe.product_id = p.id
GROUP BY oe.order_id;

执行该语句即可得到你预期的每个订单对应最大min_duration结果。

完整整合查询语句（基于你原有写法修改，兼容通用SQL逻辑） #

SELECT
    c.name AS customer_name,
    c.id AS customer_id,
    o.id AS Order_ID,
    o.created_at AS Order_CREATED_AT,
    od.max_duration AS Max_Duration
FROM "Customers" c
INNER JOIN orders o
    ON c.id = o.customer_id
INNER JOIN (
    -- 原有每个客户最新订单逻辑
    SELECT customer_id, MAX(created_at) AS max_date
    FROM orders
    GROUP BY customer_id
) latest_order
    ON o.customer_id = latest_order.customer_id
    AND o.created_at = latest_order.max_date
INNER JOIN (
    -- 第二部分的订单最大时长逻辑
    SELECT
        oe.order_id,
        MAX(p.min_duration) AS max_duration
    FROM "orderEntry" oe
    INNER JOIN product p
        ON oe.product_id = p.id
    GROUP BY oe.order_id
) od
    ON o.id = od.order_id;

PostgreSQL优化版写法（更简洁高效） #

WITH latest_orders AS (
    SELECT DISTINCT ON (customer_id)
        id AS order_id,
        customer_id,
        created_at
    FROM orders
    ORDER BY customer_id, created_at DESC
),
order_max_duration AS (
    SELECT
        oe.order_id,
        MAX(p.min_duration) AS max_duration
    FROM "orderEntry" oe
    INNER JOIN product p
        ON oe.product_id = p.id
    GROUP BY oe.order_id
)
SELECT
    c.name AS customer_name,
    c.id AS customer_id,
    lo.order_id AS Order_ID,
    lo.created_at AS Order_CREATED_AT,
    omd.max_duration AS Max_Duration
FROM latest_orders lo
INNER JOIN "Customers" c
    ON lo.customer_id = c.id
INNER JOIN order_max_duration omd
    ON lo.order_id = omd.order_id
ORDER BY c.id;

以上语句执行后输出结果和你预期的最终结果完全一致。

注意：如果你的数据库表名开启了大小写敏感，记得所有表名字段名按照你实际建表的大小写加双引号包裹，避免报表不存在的错误。

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