如何不使用GROUP BY实现按id_shift分组取最大ldm值的查询

方案1：窗口函数分区+去重（最小修改适配现有查询） #

你原来的窗口函数未加分区规则，OVER()会统计全局所有行的最大值，同时因为关联了订单表，同一个班次会匹配多个订单行才会出现重复结果。修改后查询如下：

SELECT DISTINCT
    MAX(ldm.uvalue) OVER (PARTITION BY plannedshift.id_shift) AS [max ldm],
    plannedshift.id_shift
    -- 你需要保留的其他plannedshift表列可以直接加在这里，无需修改其他逻辑
FROM plannedshift
    LEFT JOIN action ac ON plannedshift.id_shift=ac.id_shift AND ac.name = 'pickup'
    JOIN [order] ord ON ac.id_order = ord.id_order AND ac.name = 'pickup' 
    LEFT JOIN orderamount ldm ON ord.id_order = ldm.id_order AND ldm.id_unit = 5 

该方案不需要你列全所有字段做GROUP BY，DISTINCT只会合并同一个班次的重复行，同个班次的max ldm值完全一致，合并后自然每个班次仅返回一行。

方案2：QUALIFY语法过滤（适用支持该语法的数据库，性能更优） #

如果你的数据库支持QUALIFY语法（PostgreSQL、BigQuery、SQL Server 2022+等），可以直接过滤重复行，避免DISTINCT的排序开销，性能更好：

SELECT
    MAX(ldm.uvalue) OVER (PARTITION BY plannedshift.id_shift) AS [max ldm],
    plannedshift.id_shift
    -- 其他列直接添加即可
FROM plannedshift
    LEFT JOIN action ac ON plannedshift.id_shift=ac.id_shift AND ac.name = 'pickup'
    JOIN [order] ord ON ac.id_order = ord.id_order AND ac.name = 'pickup' 
    LEFT JOIN orderamount ldm ON ord.id_order = ldm.id_order AND ldm.id_unit = 5 
QUALIFY ROW_NUMBER() OVER (PARTITION BY plannedshift.id_shift ORDER BY (SELECT NULL)) = 1

这里ORDER BY (SELECT NULL)是告知数据库无需排序，随便取同班次的第一行即可，不会影响max ldm的计算结果。

方案3：子查询预聚合（大表场景性能最优） #

如果表数据量极大，可以提前聚合好每个班次的max ldm再关联，避免先JOIN再去重的开销：

SELECT
    shift_ldm.max_ldm AS [max ldm],
    ps.id_shift
    -- 其他plannedshift表的列直接添加即可
FROM plannedshift ps
LEFT JOIN (
    SELECT 
        ac.id_shift,
        MAX(ldm.uvalue) as max_ldm
    FROM action ac
    JOIN [order] ord ON ac.id_order = ord.id_order AND ac.name = 'pickup'
    LEFT JOIN orderamount ldm ON ord.id_order = ldm.id_order AND ldm.id_unit = 5 
    WHERE ac.name = 'pickup'
    GROUP BY ac.id_shift
) shift_ldm ON ps.id_shift = shift_ldm.id_shift

该方案仅在子查询中对id_shift单个字段做聚合，不需要你处理plannedshift的其他50-100个字段，完全符合你的需求，同时预聚合的执行效率是三个方案中最高的。

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