时间戳

首页 > 解决方案 > 多列的 SQL MAX 并检索每一行

sql - 多列的 SQL MAX 并检索每一行

问题描述

我想在我的数据库中获得近 20 个字段的 MAX 值。我的问题是我还想要一些具有所有这些 MAX 值的其他字段。我正在使用 PostgreSQL。

这是一个例子：

匹配表定义

CREATE TABLE "matches" (
    "id" int4 NOT NULL DEFAULT nextval('match_players_id_seq'::regclass),
    "kills" int4 NOT NULL,
    "deaths" int4 NOT NULL,
    "assists" int4 NOT NULL,
    "gamemode" int4 NOT NULL,
    PRIMARY KEY ("id")
);

匹配表示例数据

ID 杀死 死亡人数 助攻 游戏模式
1 0 0 3 1
2 1 0 2 2
3 1 12 0 3
4 7 2 27 1
5 1 4 27 2
6 2 3 1 3 
INSERT INTO "matches" ("id", "kills", "deaths", "assists", "gamemode") VALUES
(1, 0, 0, 3, 1),
(2, 1, 0, 2, 2),
(3, 1, 12, 0, 3),
(4, 7, 2, 27, 1),
(5, 1, 4, 27, 2),
(6, 2, 3, 1, 3);

上述数据中我想要的结果（表格或 JSON 样式）

什么 数量 游戏模式 ID
“杀” 7 1 4
“死亡人数” 12 3 3
“助攻” 27 1 4 
{
  "maxKills": {"id": 4, "kills": 7, "gamemode": 1},
  "maxDeaths": {"id": 3, "deaths": 12, "gamemode": 3}
  "maxAssists": {"id": 4, "assists": 27, "gamemode": 1} // Get the first one if 2 are equal
}

这是简化的。这是一个小pastebin，可以让您了解真正的查询：https
://pastebin.com/eT8MNKKe 另一个带有@Erwin 答案实现的pastebin：https ://pastebin.com/2B8imJTj

两年前我已经发布了这个问题，但我使用了 NoSQL 数据库（Mongo）：Get objects contains max values for multiple fields using aggregation in mongodb

这是我的想法，我不知道它是否是最好的：
做 2 个查询。第一个获取 20 列的 MAX 值，第二个获取包含 20 个聚合 MAX 值的 WHERE 子句的行。我只是不确定当多行具有相同的 MAX 值时该怎么做。

标签： sqlpostgresqlgreatest-n-per-groupunion-all

解决方案

您想要获取具有最大值的行。SQL 结果是由列和行组成的表。这是一种纯 SQL 方法：

select what, amount, gamemode, id
from
(
  select id, gamemode, 'kills' as what, kills as amount, max(kills) over () as max_amount from mytable
  union all
  select id, gamemode, 'deaths' as what, deaths as amount, max(deaths) over () as max_amount from mytable
  union all
  select id, gamemode, 'assists' as what, assists as amount, max(assists) over () as max_amount from mytable
  union all
  ...
) all_candidates
where amount = max_amount;

可能有一些内置的 JSON 函数可以将此结果转换为 JSON。我不知道。也许其他人可以回答这个问题。

推荐阅读