SQL Server下按规则为每个PersonId返回单行的SQL查询编写方法
SQL Server 环境下的合规查询实现
原有写法的不兼容原因
你之前使用的隐式连接+非标准GROUP BY逻辑,依赖部分数据库对分组字段的宽松校验规则,SQL Server 从设计上就不支持这种「未将所有非聚合查询字段加入分组集合」的写法;同时原有HAVING逻辑本身存在边界漏洞:当同一个PersonId下存在多条匹配记录时,会返回多余行,且非分组字段的取值无规则,无法保证结果和预期一致。
兼容全版本SQL Server的实现
使用窗口函数做行级优先级排序,逻辑完全匹配业务规则,兼容所有目前受官方支持的SQL Server版本:
WITH ranked_records AS ( SELECT p.PersonId, a.City, a.Type, a.UpdateDate, ROW_NUMBER() OVER ( -- 按每个PersonId独立划分排序分区 PARTITION BY p.PersonId ORDER BY -- Type为P的记录设置最高排序优先级 CASE WHEN a.Type = 'P' THEN 0 ELSE 1 END, -- 同优先级下选取更新时间最早的记录 a.UpdateDate ASC ) AS row_rank FROM person p INNER JOIN address a ON p.PersonId = a.PersonId ) SELECT PersonId, City, Type, UpdateDate FROM ranked_records WHERE row_rank = 1;
等效简化写法
如果使用SQL Server 2005及以上版本,可以借助TOP 1 WITH TIES特性省略公共表达式,代码更简洁,执行效果和上面的方案完全一致:
SELECT TOP 1 WITH TIES p.PersonId, a.City, a.Type, a.UpdateDate FROM person p INNER JOIN address a ON p.PersonId = a.PersonId ORDER BY p.PersonId, CASE WHEN a.Type = 'P' THEN 0 ELSE 1 END, a.UpdateDate ASC
两种写法针对你给出的示例数据,返回结果和预期完全匹配:存在Type='P'记录的PersonId会优先返回对应P类记录;无P类记录的PersonId(如示例中的140),会自动选取该分组下UpdateDate最小的M类记录返回。
内容的提问来源于stack exchange,提问作者Karl
相关产品推荐
相关产品推荐

