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

作者: 狗头军师
发布时间: 2025-01-05 05:49:39 (1天前)

2 条回复

1#
回复此人
v-star*위위 | 2020-08-06 16-47

在Oracle 9.2+（不是最初所说的8i +），SQL Server 2005 +，PostgreSQL 8.4 +，DB2，Firebird 3.0 +，Teradata，Sybase，Vertica上： ``` WITH summary AS ( SELECT p.id, p.customer, p.total, ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) AS rk FROM PURCHASES p) SELECT s.* FROM summary s WHERE s.rk = 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 ```

编辑

登录后才能参与评论