如何提取交易列表中最大交易及分组高值完整交易？是否有更优写法？

给定如下交易表：

需要提取两类完整交易记录：

• 每个客户的最高价值交易
• 每个产品的最高价值交易

目前可通过子查询+JOIN的方式实现，比如提取每个客户最高价值交易的写法：

-- 获取每个客户的最高交易金额
SELECT CustID, MAX(Value) AS highest_value
FROM Table
GROUP BY CustID

-- 关联原表获取完整记录
SELECT t.*
FROM Table t
JOIN (
    SELECT CustID, MAX(Value) AS highest_value
    FROM Table
    GROUP BY CustID
) sq ON t.CustID = sq.CustID 
    AND t.Value = sq.highest_value

请问有没有更简洁的写法实现相同功能？

更简洁的实现方式 #

1. 窗口函数（主流数据库推荐方案） #

窗口函数是分组取最值场景的最优解，语法简洁逻辑清晰，MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库均支持。

提取每个客户的最高价值交易

用ROW_NUMBER()或RANK()按客户分组，对交易价值降序排序后取首位记录：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY Value DESC) AS rn
    FROM Table
) t
WHERE rn = 1;

• 若同一客户有多条价值相同的最高交易，ROW_NUMBER()会随机保留一条；RANK()则返回所有等值记录，可按需替换。

提取每个产品的最高价值交易

仅需将分组字段替换为ItemID：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY Value DESC) AS rn
    FROM Table
) t
WHERE rn = 1;

2. CTE简化窗口函数写法 #

支持CTE的数据库中，可通过公共表表达式让代码更易读：

WITH ranked_transactions AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY Value DESC) AS rn
    FROM Table
)
SELECT * FROM ranked_transactions WHERE rn = 1;

3. 兼容旧版MySQL（无窗口函数） #

对于MySQL 5.x等不支持窗口函数的版本，可使用关联子查询实现，写法比原JOIN方式更简洁：

SELECT t.*
FROM Table t
WHERE t.Value = (
    SELECT MAX(Value)
    FROM Table
    WHERE CustID = t.CustID
);

替换CustID为ItemID即可适配产品维度的查询。

内容的提问来源于stack exchange，提问作者Feargal Hogan