如何在WHERE子句中使用MAX()筛选最大值记录？

编写了创建视图Handoff的SQL查询，尝试通过WHERE子句筛选出NOTICE.[MaxValueField]值最大的记录，但最后一行的MAX筛选部分报错，其余WHERE条件均可正常工作。

原SQL代码 #

CREATE VIEW Handoff 
AS
    SELECT 
        REALMAST.[PIN] AS PIN,
        NOTICE.[NOTICETYPE] AS CHANGE_REASON,
        NOTE.[NOTETYPE] AS LAST_NOTE
    FROM 
        [t1] AS SALEHIST
    INNER JOIN 
        [t2] AS REALMAST ON SALEHIST.[key] = REALMAST.[key]
    INNER JOIN 
        [t3] AS PARCEL_EXTENSION ON REALMAST.[key] = PARCEL_EXTENSION.[key]
    INNER JOIN 
        [t4] AS NOTICE ON PARCEL_EXTENSION.[key] = NOTICE.[key]
    INNER JOIN 
        [t5] AS NOTE ON NOTICE.[key] = NOTE.[key] 
    WHERE 
        SALEHIST.[CURRENT_OWNER] = 1 
        AND PARCEL_EXTENSION.[USER] = 1 
        AND NOTE.[NOTE_NUM] = 1 
        AND (SELECT MAX(NOTICE.[MaxValueField] FROM NOTICE)

错误提示 #

Msg 4145, Level 15, State 1, Procedure Property_AdminHandoff, Line 15 [Batch Start Line 0]
An expression of non-boolean type specified in a context where a condition is expected, near ')'

最小复现示例 #

需求：获取NOH_LINE_NUM最大值对应的记录。

问题分析与解决方案 #

错误原因 #

1. 子查询语法错误：SELECT MAX(NOTICE.[MaxValueField] FROM NOTICE)缺少闭合括号，正确写法应为SELECT MAX(NOTICE.[MaxValueField]) FROM NOTICE
2. 逻辑缺失：WHERE子句需要布尔判断，你需要将当前行的NOTICE.[MaxValueField]与最大值做相等匹配，而非直接返回最大值。

修正后的SQL方案 #

方案1：子查询匹配全局最大值

如果要筛选整个NOTICE表中MaxValueField最大的记录，可直接将字段与最大值子查询做匹配：

CREATE VIEW Handoff 
AS
    SELECT 
        REALMAST.[PIN] AS PIN,
        NOTICE.[NOTICETYPE] AS CHANGE_REASON,
        NOTE.[NOTETYPE] AS LAST_NOTE
    FROM 
        [t1] AS SALEHIST
    INNER JOIN 
        [t2] AS REALMAST ON SALEHIST.[key] = REALMAST.[key]
    INNER JOIN 
        [t3] AS PARCEL_EXTENSION ON REALMAST.[key] = PARCEL_EXTENSION.[key]
    INNER JOIN 
        [t4] AS NOTICE ON PARCEL_EXTENSION.[key] = NOTICE.[key]
    INNER JOIN 
        [t5] AS NOTE ON NOTICE.[key] = NOTE.[key] 
    WHERE 
        SALEHIST.[CURRENT_OWNER] = 1 
        AND PARCEL_EXTENSION.[USER] = 1 
        AND NOTE.[NOTE_NUM] = 1 
        AND NOTICE.[MaxValueField] = (SELECT MAX(n.[MaxValueField]) FROM [t4] AS n)

方案2：窗口函数分组取最大值（更灵活）

如果需要按关联主键（如REALMAST.[key]）分组，获取每组内MaxValueField最大的记录，推荐使用窗口函数：

CREATE VIEW Handoff 
AS
WITH RankedData AS (
    SELECT 
        REALMAST.[PIN] AS PIN,
        NOTICE.[NOTICETYPE] AS CHANGE_REASON,
        NOTE.[NOTETYPE] AS LAST_NOTE,
        -- 按分组字段分区，按MaxValueField倒序排名
        ROW_NUMBER() OVER (PARTITION BY REALMAST.[key] ORDER BY NOTICE.[MaxValueField] DESC) AS rn
    FROM 
        [t1] AS SALEHIST
    INNER JOIN 
        [t2] AS REALMAST ON SALEHIST.[key] = REALMAST.[key]
    INNER JOIN 
        [t3] AS PARCEL_EXTENSION ON REALMAST.[key] = PARCEL_EXTENSION.[key]
    INNER JOIN 
        [t4] AS NOTICE ON PARCEL_EXTENSION.[key] = NOTICE.[key]
    INNER JOIN 
        [t5] AS NOTE ON NOTICE.[key] = NOTE.[key] 
    WHERE 
        SALEHIST.[CURRENT_OWNER] = 1 
        AND PARCEL_EXTENSION.[USER] = 1 
        AND NOTE.[NOTE_NUM] = 1 
)
SELECT PIN, CHANGE_REASON, LAST_NOTE
FROM RankedData
WHERE rn = 1;

针对最小复现示例的查询 #

如果要获取NOH_MNC+NOH_PID分组下NOH_LINE_NUM最大的记录，可使用以下SQL：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY NOH_MNC, NOH_PID ORDER BY NOH_LINE_NUM DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

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