当前位置：文江博客话题详情

SQL GROUP BY问题

发布于 2024-10-11 10:46:39 字数 309 浏览 0 评论 0原文

我有一张桌子：

<代码> 组 ID 数据
1  a    10
2  a    20
3  b    10
4  b    20

我想获取按“group”分组的最大“data”值的记录ID，即

编号 2 4

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

七色彩虹 2024-10-18 10:46:39

使用 CTE 的更现代的答案：

;WITH Numbered as (
    SELECT ID,ROW_NUMBER() OVER (PARTITION BY group ORDER BY data desc) rn FROM Table
)
SELECT ID from Numbered where rn=1

PostgreSQL 有一些相当不错的在线文档，请查看窗口函数和WITH查询。在这种情况下，我们根据表中的行所属的组对它们进行分区。在每个分区中，我们根据数据列对行进行编号（行号 1 分配给最高数据值）。

在外部查询中，我们只要求在其分区内分配行号1的行，如果遵循逻辑，它一定是每个组内的最大数据值。

如果您需要处理关系（即，如果一个组中的多行都具有该组的最大数据值，并且您希望两者都出现在结果集中），您可以从 ROW_NUMBER() 到 RANK()

A more modern answer using CTEs:

;WITH Numbered as (
    SELECT ID,ROW_NUMBER() OVER (PARTITION BY group ORDER BY data desc) rn FROM Table
)
SELECT ID from Numbered where rn=1

PostgreSQL has some pretty decent documentation online, look at window functions and WITH queries. In this case, we partition the rows in the table based on which group they belong to. Within each partition, we number the rows based on their data column (with row number 1 being assigned to the highest data value).

In the outer query, we just ask for the rows which were assigned row number 1 within their partition, which if you follow the logic, it must be the maximum data value within each group.

If you need to deal with ties (i.e. if multiple rows within a group both have the maximum data value for the group, and you want both to appear in your result set), you could switch from ROW_NUMBER() to RANK()

回复收藏 0 原文

ゝ偶尔ゞ 2024-10-18 10:46:39

便携式解决方案：

SELECT T1.id
FROM yourtable T1
JOIN (
    SELECT grp, MAX(data) AS data
    FROM yourtable
    GROUP BY grp
) T2
WHERE T1.grp = T2.grp AND T1.data = T2.data

PostgreSQL解决方案：

SELECT DISTINCT ON (grp) id
FROM yourtable
ORDER BY grp, data DESC;

PS：我将列名从group更改为grp，因为group是保留字。如果您确实想要使用group，则必须引用它。

Portable solution:

SELECT T1.id
FROM yourtable T1
JOIN (
    SELECT grp, MAX(data) AS data
    FROM yourtable
    GROUP BY grp
) T2
WHERE T1.grp = T2.grp AND T1.data = T2.data

PostgreSQL solution:

SELECT DISTINCT ON (grp) id
FROM yourtable
ORDER BY grp, data DESC;

PS: I changed the column name from group to grp because group is a reserved word. If you really want to use group you'll have to quote it.

回复收藏 0 原文

冷血 2024-10-18 10:46:39

SELECT Id
FROM Table t1 JOIN (Select Group, Max(Data) Data from Table 
                   group by group) t2
WHERE t1.Group = t2.Group
  AND t1.Data = t2.Data

SELECT Id
FROM Table t1 JOIN (Select Group, Max(Data) Data from Table 
                   group by group) t2
WHERE t1.Group = t2.Group
  AND t1.Data = t2.Data

回复收藏 0 原文

~没有更多了~