SQL查询中如何提取每个AM.Id首次出现的对应RPId值

你写的这段SQL本身的排序逻辑就已经适配分组取首行的要求，不需要额外调整排序规则，直接基于现有结果做分组截取就行，两种适配不同数据库版本的写法可以直接用：

支持窗口函数的通用写法 #

用ROW_NUMBER()窗口函数给每个AM.Id分组内的行按SubVersion升序编排名次，直接取名次为1的行就是每个分组下首次出现的记录，SQL Server 2012+、MySQL 8.0+、PostgreSQL都能直接跑：

WITH base_result AS (
    select  
        AM.Id AS AMId, 
        convert(int, sub.Version) as SubVersion, 
        RP.Id as RPId 
    from sub 
    left join ZRP as RP on RP.sid = sub.id
    left join ZAM as AM on AM.Id = RP.AmId
    where SUB.Name = 'xxxx' and AmId <> ''
),
sorted_group AS (
    SELECT 
        AMId,
        SubVersion,
        RPId,
        ROW_NUMBER() OVER (PARTITION BY AMId ORDER BY SubVersion) AS rn
    FROM base_result
)
SELECT AMId, SubVersion, RPId
FROM sorted_group
WHERE rn = 1;

无窗口函数的低版本适配写法 #

先算出每个AM.Id对应的最小SubVersion值，再回表关联拿到对应的RPId即可，兼容SQL Server 2008这类老版本数据库：

select  
    AM.Id as AMId,
    convert(int, sub.Version) as SubVersion,
    RP.Id as RPId 
from sub 
inner join ZRP as RP on RP.sid = sub.id
inner join ZAM as AM on AM.Id = RP.AmId
inner join (
    -- 预计算每个AM.Id下的最小SubVersion
    SELECT 
        AM.Id AS AMId,
        MIN(convert(int, sub.Version)) AS min_version
    from sub 
    left join ZRP as RP on RP.sid = sub.id
    left join ZAM as AM on AM.Id = RP.AmId
    where SUB.Name = 'xxxx' and AmId <> ''
    GROUP BY AM.Id
) version_calc ON 
    AM.Id = version_calc.AMId 
    AND convert(int, sub.Version) = version_calc.min_version
where SUB.Name = 'xxxx' and AmId <> ''

实用提示：原查询用了左连接，但是过滤条件带了AmId<>''，那些因为左连接没匹配上导致AmId为null的行都会被过滤掉，实际等效于内连接，直接把连接类型改成inner join不会改变最终结果，还能减少数据库的无效扫描开销。

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