每组具有最大值的行-SQLite

kulphzqa 于 2022-11-15 发布在 SQLite

关注(0)|答案(3)|浏览(147)

给出一个包含columns(name, lat, lon, population, type)的表，其中每个名称都有很多行，我想选择按名称分组的行，其中人口是最高的。如果我将自己限制在名称和人口范围内，下面的内容将会起作用

SELECT name, Max(population) 
FROM table WHERE name IN ('a', 'b', 'c') 
GROUP BY name;

但我希望其他列-lat, lon, type-也出现在结果中。如何使用SQLite实现这一点？

sqlite

来源：https://stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite

3条答案

按热度按时间

0ejtzxu11#

SQLite允许您只列出您想要的其他列；它们保证来自具有最大值的行：

SELECT name, lat, lon, Max(population), type
FROM table
WHERE name IN ('a', 'b', 'c')
GROUP BY name;

文件中写道：
当聚合函数为min()或max()时，会进行特殊处理。示例：

SELECT a, b, max(c) FROM tab1 GROUP BY a;

在聚合查询中使用min()或max()聚合函数时，结果集中的所有裸列都从也包含最小值或最大值的输入行中获取值。

赞(0）回复(0）举报 2022-11-15

4jb9z9bj2#

对结果进行连接以获得完整表记录

SELECT t1.*
FROM your_table t1
JOIN 
(
    SELECT name, Max(population) as max_population
    FROM your_table 
    WHERE name IN ('a', 'b', 'c') 
    GROUP BY name
) t2 ON t1.name = t2.name
    and t1.population = t2.max_population

赞(0）回复(0）举报 2022-11-15

vbopmzt13#

RANK或ROW_NUMBER窗口函数

尽管如https://stackoverflow.com/a/48328243/895245所述，max保证可以在SQLite上运行，但以下方法似乎更便携、更具通用性：

SELECT *
FROM (
    SELECT
      ROW_NUMBER() OVER (
        PARTITION BY "name"
        ORDER BY "population" DESC
      ) AS "rnk",
      *
    FROM "table"
    WHERE "name" IN ('a', 'b', 'c')
  ) sub
WHERE
  "sub"."rnk" = 1
ORDER BY
  "sub"."name" ASC,
  "sub"."population" DESC

完全相同的代码可以在这两种代码上运行：

SQLite 3.34.0
PostgreSQL 14.3

此外，我们可以轻松地修改该查询以涵盖以下用例：

如果将ROW_NUMBER()替换为RANK()，则如果超过一行达到最大值，则返回max的所有关联
如果将"sub"."rnk" = 1替换为"sub"."rnk" <= n，您可以获得每个组的前n名，而不仅仅是前1名

赞(0）回复(0）举报 2022-11-15

我来回答

每组具有最大值的行-SQLite

3条答案

相关问题

热门标签

最新问答