SQL Server中Oracle ANY_VALUE(...) KEEP语法的等效实现方案

问题背景 #

在Oracle SQL中，我们可以通过KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)语法，在聚合查询（带GROUP BY）中直接获取非分组列的对应值——比如按国家分组后，获取每个国家人口最多的城市名称，无需子查询、连接或WITH子句。示例Oracle代码如下：

-- Oracle
-- 查询拥有多个城市的国家中，人口最多的城市名称
select
    country,
    count(*),
    max(population),
    any_value(city)
        keep (dense_rank first order by population desc, city desc)
from
    cities
group by
    country
having
    count(*) > 1

注：这里用ANY_VALUE替代MAX是为了可读性更强；若存在人口并列的情况，可通过ORDER BY后追加city desc来打破并列，确保结果确定。

SQL Server的等效方案 #

SQL Server中没有直接对应KEEP子句的语法，但可以通过窗口函数FIRST_VALUE结合聚合函数实现相同效果，且完全在单个SELECT查询内完成，无需子查询、连接或WITH子句：

-- SQL Server
-- 查询拥有多个城市的国家中，人口最多的城市名称
select
    country,
    count(*) as city_count,
    max(population) as max_population,
    max(
        first_value(city) over (
            partition by country 
            order by population desc, city desc 
            rows between unbounded preceding and unbounded following
        )
    ) as most_populous_city
from
    cities
group by
    country
having
    count(*) > 1

方案说明 #

• first_value(city) over (...)：按国家分区，在每个分区内按人口降序、城市名降序排序，取排序后的第一个城市名。rows between unbounded preceding and unbounded following确保窗口包含分区内所有行，避免默认窗口范围（仅当前行及之前行）导致的错误。
• 外层用max()聚合：同一国家分组内的所有行，first_value返回的结果完全相同，用max()（或min()）可将窗口函数结果转换为聚合值，满足GROUP BY查询的语法要求。

补充：SQL Server 2022+简化写法 #

如果使用SQL Server 2022及以上版本，可用ANY_VALUE函数替代max()，写法更接近Oracle用法，可读性更强：

-- SQL Server 2022+
select
    country,
    count(*) as city_count,
    max(population) as max_population,
    any_value(
        first_value(city) over (
            partition by country 
            order by population desc, city desc 
            rows between unbounded preceding and unbounded following
        )
    ) as most_populous_city
from
    cities
group by
    country
having
    count(*) > 1

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