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

具体来说,如果我有一个如下所示的采购表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个客户的最大购买量(总购买量)。类似于:

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

预期输出:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 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

任何数据库都支持:

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

  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

其他回答

非常快速的解决方案

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

如果表是按id索引的,则速度非常快:

create index purchases_id on purchases (id);

这是一个常见的最大的每组问题,它已经有了经过充分测试和高度优化的解决方案。就我个人而言,我更喜欢比尔·卡尔温(Bill Karwin)的左联解决方案(原始帖子中有很多其他解决方案)。

请注意,MySQL手册中可以意外地找到解决这个常见问题的一系列解决方案——尽管您的问题是在Postgres中,而不是MySQL中,但给出的解决方案应该适用于大多数SQL变体。请参见常见查询的示例::保持某一列的组最大值的行。

查询:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

这是怎么回事!(我去过那里)

我们希望确保每次购买的总金额最高。


一些理论知识(如果您只想了解查询,请跳过此部分)

让Total是一个函数T(customer,id),其中它返回一个给定名称和id的值为了证明给定的总数(T(customer,id))是最高的,我们必须证明我们想证明

∀x T(customer,id)>T(customer,x)(这个总数高于所有其他该客户的总计)

OR

∃x T(customer,id)<T(customers,x)(不存在更高的总数该客户)

第一种方法需要我们获取我不太喜欢的名字的所有记录。

第二个将需要一个聪明的方式来表示,没有比这个更高的记录了。


返回SQL

如果我们在表的名称和总数小于连接表的情况下留下连接表:

LEFT JOIN purchases as p 
ON 
p.customer = purchases.customer 
AND 
purchases.total < p.total

我们确保要加入的同一用户的另一条记录总数较高的所有记录:

+--------------+---------------------+-----------------+------+------------+---------+
| purchases.id |  purchases.customer | purchases.total | p.id | p.customer | p.total |
+--------------+---------------------+-----------------+------+------------+---------+
|            1 | Tom                 |             200 |    2 | Tom        |     300 |
|            2 | Tom                 |             300 |      |            |         |
|            3 | Bob                 |             400 |    4 | Bob        |     500 |
|            4 | Bob                 |             500 |      |            |         |
|            5 | Alice               |             600 |    6 | Alice      |     700 |
|            6 | Alice               |             700 |      |            |         |
+--------------+---------------------+-----------------+------+------------+---------+

这将有助于我们在不需要分组的情况下筛选每次购买的最高总额:

WHERE p.total IS NULL
    
+--------------+----------------+-----------------+------+--------+---------+
| purchases.id | purchases.name | purchases.total | p.id | p.name | p.total |
+--------------+----------------+-----------------+------+--------+---------+
|            2 | Tom            |             300 |      |        |         |
|            4 | Bob            |             500 |      |        |         |
|            6 | Alice          |             700 |      |        |         |
+--------------+----------------+-----------------+------+--------+---------+

这就是我们需要的答案。

这样对我来说很有效:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

选择每篇文章的最高价格

我通过窗口函数dbfiddle的方法:

将每组的row_number()分配给(按agreement_id、order_id划分)为nrow只取组:filter中的第一行(其中nrow=1)

with intermediate as (select 
 *,
 row_number() over ( partition by agreement_id, order_id ) as nrow,
 (sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma,
from <your table>)

select 
  *,
  sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)
from intermediate