查找最大值并在SQL Server中从其他字段显示相应的值 [英] Find max value and show corresponding value from different field in SQL server

问题描述

我有一张表格，其中包含有关城市的数据，其中包括城市名称，人口和与我的问题无关的其他字段.

I have a table with data about cities that includes their name, population and other fields irrelevant to my question.

ID      Name    Population
1       A       45667   
2       B       123456  
3       C       3005    
4       D       13769   

要找到最大人口是基本的，但是我需要一个结果表，其中一列具有最大人口，另一列具有相应的城市名称

To find the max population is basic, but I need a resulting table that has the max population in one column, and the corresponding city's name in another column

Population      Name    
123456          B       

我已经浏览了

I've looked through similar questions, but for some reason the answers look over-complicated. Is there a way to write the query in 1 or 2 lines?

推荐答案

有几种方法可以做到:

WHERE子句中的过滤器:

select id, name, population
from yourtable
where population in (select max(population)
                     from yourtable)

或子查询:

select id, name, population
from yourtable t1
inner join
(
  select max(population) MaxPop
  from yourtable
) t2
  on t1.population = t2.maxpop;

或者您可以使用TOP WITH TIES.如果没有关系，则可以删除with ties.这将包括所有具有相同人口值的行:

Or you can use TOP WITH TIES. If there can be no ties, then you can remove the with ties. This will include any rows that have the same population value:

select top 1 with ties id, name, population
from yourtable
order by population desc

由于您使用的是SQL Server，因此还可以使用排名函数来获取结果:

Since you are using SQL Server you can also use ranking functions to get the result:

select id, name, population
from
(
  select id, name, population,
    row_number() over(order by population desc) rn
  from yourtable
) src
where rn = 1

请全部参见带有演示的SQL Fiddle .

作为排名功能的补充说明，您可能希望使用dense_rank()而不是row_number().然后，如果您有不止一个人口相同的城市，您将获得两个城市的名称. (请参见演示)

As a side note on the ranking function, you might want to use dense_rank() instead of row_number(). Then in the event you have more than one city with the same population you will get both city names. (See Demo)

这篇关于查找最大值并在SQL Server中从其他字段显示相应的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！