英文:
SQL Server equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
问题
以下是翻译好的内容：
"Is there equivalent functionality in SQL Server?" -> "SQL Server中是否有类似的功能？"
英文:
There's a technique in Oracle SQL that can be used to simplify aggregation queries.
Scenario: We've aggregated on a particular column — but we actually want to know information from a different column.
There are a number of ways to achieve that using SQL. I'm looking for a solution that lets me do it in a calculated column -- all within a single SELECT query (no subqueries, joins, WITH, etc.).
Like this:
--Oracle
--For a given country, what city has the highest population? (where the country has more than one city)
--Include the city name as a column.
select
country,
count(*),
max(population),
any_value(city)
keep (dense_rank first order by population desc)
from
cities
group by
country
having
count(*) > 1
As shown above, the following column can bring in the city name, even though the city name isn't in the GROUP BY:
any_value(city) keep (dense_rank first order by population desc)
Is there equivalent functionality in SQL Server?
Related:
- YouTube - The KEEP clause will KEEP your SQL queries SIMPLE (Oracle)
- db-oriented.com: ANY_VALUE and FIRST/LAST (KEEP)
- Stack Overflow: Explanation of KEEP in Oracle FIRST/LAST
Edit:
I changed MAX() to ANY_VALUE(), since I think ANY_VALUE() is easier to read.
Ties can be broken by adding , city desc to the order by, making it deterministic:
any_value(city) keep (dense_rank first order by population desc, city desc)
答案1
得分: 2
SQL Server没有实现这个功能。
我正在寻找一个让我可以在一个计算列中完成的解决方案
-- 全部在单个SELECT查询内（没有子查询、连接、WITH等）。
这是可能的，但不太美观（DB Fiddle）。
假设人口是正整数，那么您可以使用以下方法（请参考基于连接的解决方案，了解此方法背后的思路）：
select
country,
count(*),
max(population),
SUBSTRING(MAX(FORMAT(population, 'D10') + city), 11, 8000)
from
cities
group by
country
having
count(*) > 1
英文:
SQL Server does not implement this.
> I'm looking for a solution that lets me do it in a calculated column
> -- all within a single SELECT query (no subqueries, joins, WITH, etc.)
It's possible but not pretty (DB Fiddle).
Assuming population is a positive integer then you could use the below (See Solution Based on Concatenation for the idea behind this approach)
select
country,
count(*),
max(population),
SUBSTRING(MAX(FORMAT(population, 'D10') + city), 11, 8000)
from
cities
group by
country
having
count(*) > 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论