编程基础网 / 编程问答 / 数据库问题 / 正文

在每个 GROUP BY 组中选择第一行?

编程基础网数据库问题

2022-01-01

Select first row in each GROUP BY group?(在每个 GROUP BY 组中选择第一行?)

本文介绍了在每个 GROUP BY 组中选择第一行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

正如标题所示，我想选择用 GROUP BY 分组的每组行的第一行.

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

具体来说，如果我有一个如下所示的 purchases 表:

Specifically, if I've got a purchases table that looks like this:

SELECT * FROM purchases;

我的输出:

<头>

身份证	客户	总计
1	乔	5
2	莎莉	3
3	乔	2
4	莎莉	1

我想查询每个客户的最大购买量(total)的id.像这样的:

I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

预期输出:

<头>

FIRST(id)	客户	FIRST(总计)
1	乔	5
2	莎莉	3

在支持CTE和窗口函数:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

任何数据库都支持:

但你需要添加逻辑来打破关系:

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

这篇关于在每个 GROUP BY 组中选择第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

编程基础网

本文标题为：在每个 GROUP BY 组中选择第一行?