如何实现按客户分组取最近3笔订单金额并排序输出前15条SQL查询

• 第一步：统一日期格式，将字符串类型的ordered_date转换为日期类型，避免排序错误
• 第二步：对每个客户的所有订单按下单日期倒序排名，筛选出排名前3的订单（即最近3笔）
• 第三步：将每个客户的多笔订单金额做行转列处理，按时间从近到远输出
• 第四步：按客户姓名升序排序，最终取前15条记录

• 子查询按cust_name分组后返回多条最大日期，外层查询直接引用该子查询会触发「子查询返回多行」的语法错误
• 没有实现提取最近3笔订单金额的核心逻辑，仅查询了每个客户的最大下单日期
• 缺少按客户分组聚合订单金额、按cust_name升序排序的对应逻辑

支持窗口函数的版本（MySQL 8.0+/PostgreSQL/Oracle通用） #

如果需要和样例格式一致，直接拼接客户名和对应金额，代码如下：

WITH ranked_orders AS (
    -- 给每个客户的订单按日期倒序排名
    SELECT 
        cust_name,
        order_amount,
        ROW_NUMBER() OVER (
            PARTITION BY cust_name 
            ORDER BY STR_TO_DATE(ordered_date, '%m/%d/%Y') DESC
        ) AS order_rank
    FROM order_table
)
-- 聚合每个客户前3笔订单金额
SELECT 
    CONCAT(cust_name, ' ', GROUP_CONCAT(order_amount ORDER BY order_rank SEPARATOR ' ')) AS output
FROM ranked_orders
WHERE order_rank <= 3
GROUP BY cust_name
ORDER BY cust_name ASC
LIMIT 15;

如果需要把3笔金额分成独立的列输出，代码如下：

WITH ranked_orders AS (
    SELECT 
        cust_name,
        order_amount,
        ROW_NUMBER() OVER (
            PARTITION BY cust_name 
            ORDER BY STR_TO_DATE(ordered_date, '%m/%d/%Y') DESC
        ) AS order_rank
    FROM order_table
)
SELECT 
    cust_name,
    MAX(CASE WHEN order_rank = 1 THEN order_amount END) AS latest_amount_1,
    MAX(CASE WHEN order_rank = 2 THEN order_amount END) AS latest_amount_2,
    MAX(CASE WHEN order_rank = 3 THEN order_amount END) AS latest_amount_3
FROM ranked_orders
WHERE order_rank <= 3
GROUP BY cust_name
ORDER BY cust_name ASC
LIMIT 15;

MySQL 5.x 无窗口函数兼容版本 #

SELECT 
    CONCAT(t1.cust_name, ' ', GROUP_CONCAT(
        t1.order_amount 
        ORDER BY STR_TO_DATE(t1.ordered_date, '%m/%d/%Y') DESC 
        SEPARATOR ' '
    )) AS output
FROM order_table t1
WHERE (
    -- 关联统计当前订单在该客户的订单中按倒序排的位次
    SELECT COUNT(*) 
    FROM order_table t2 
    WHERE t2.cust_name = t1.cust_name 
    AND STR_TO_DATE(t2.ordered_date, '%m/%d/%Y') >= STR_TO_DATE(t1.ordered_date, '%m/%d/%Y')
) <= 3
GROUP BY t1.cust_name
ORDER BY t1.cust_name ASC
LIMIT 15;

以上代码运行后输出结果和你给出的样例完全匹配。

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