如何编写sql查询以获取使用聚合函数的查询中的主键

My table structure looks like this:

我的表结构如下所示：

I want id(primary key) of city with max population statewise.

我想要最大人口状态的城市的id（主键）。

If there is a tie in max population in particular state then any one id of those rows should be selected.

如果特定状态的最大人口中存在平局，则应选择这些行中的任何一个id。

5 个解决方案

#1

Use window function for this:

使用窗口功能：

with cte as(select *, row_number() 
                      over(partition by state order by population desc, id) rn from table)
select * from cte where rn = 1

If there can be several rows with max population then you can try rank function instead of row_number:

如果可以有多行具有最大人口数，那么您可以尝试排名函数而不是row_number：

with cte as(select *, rank() 
                      over(partition by state order by population desc) rn from table)
select * from cte where rn = 1

#2

required sql query:

必需的SQL查询：

SELECT *
FROM(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY state ORDER BY population DESC) AS RowNum
    FROM dbo.tbl_city
    ) s
WHERE s.RowNum = 1

#3

Try this:

尝试这个：

select id, state, population
from tbl_city a
where id = (select top 1 id from tbl_city where state = a.state order by population DESC)

#4

You can try this code:

你可以试试这段代码：

SELECT * FROM (
   SELECT id, state, population,
      ROW_NUMBER() OVER(PARTITION BY state ORDER BY population DESC) AS rn
   FROM tbl_city) AS A
WHERE rn = 1

#5

Try this,

尝试这个，

SELECT id,
       state,
       population
FROM   #yourtable A
WHERE  population IN(SELECT Max(population) AS population
                     FROM   #yourtable A1
                     WHERE  A.state = A1.state
                     GROUP  BY state)

#1