Oracle数据库中如何获取各客户最大值记录及上一条记录的Total数据

核心思路 #

通过Oracle的分析函数对每个客户的记录排序并标记序号，分别提取最大值记录和其前一条记录的字段，最后关联合并结果。

SQL代码实现 #

假设以Contract_id作为"最大值记录"的判断维度（若实际是时间或Total总和，替换对应排序字段即可）：

WITH ranked_data AS (
    SELECT 
        "Client Name",
        Contract_id,
        Total1,
        Total2,
        Total3,
        Total4,
        -- 按客户分组，Contract_id降序排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY "Client Name" ORDER BY Contract_id DESC) AS row_num
    FROM your_table_name -- 替换为你的实际表名
),
client_max AS (
    SELECT 
        "Client Name",
        Contract_id AS max_contract_id,
        Total1 AS max_total1,
        Total2 AS max_total2,
        Total3 AS max_total3,
        Total4 AS max_total4
    FROM ranked_data
    WHERE row_num = 1 -- 取每个客户的最大Contract_id记录
),
client_prev AS (
    SELECT 
        "Client Name",
        Total1 AS prev_total1,
        Total2 AS prev_total2,
        Total3 AS prev_total3,
        Total4 AS prev_total4
    FROM ranked_data
    WHERE row_num = 2 -- 取每个客户的上一条记录
)
SELECT 
    cm."Client Name",
    cm.max_contract_id,
    cm.max_total1,
    cm.max_total2,
    cm.max_total3,
    cm.max_total4,
    cp.prev_total1,
    cp.prev_total2,
    cp.prev_total3,
    cp.prev_total4
FROM client_max cm
LEFT JOIN client_prev cp ON cm."Client Name" = cp."Client Name";

自定义调整说明 #

• 如果"最大值记录"是按Total字段总和判断，修改ROW_NUMBER()的排序规则：

  ROW_NUMBER() OVER (PARTITION BY "Client Name" ORDER BY (Total1+Total2+Total3+Total4) DESC) AS row_num
  

• 若客户仅有一条记录，上一条记录的Total字段会返回NULL，符合业务逻辑。

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