SQL Server 2017如何获取每种物料的最新3条订单数据

现有数据表 #

orders表 #

materials表 #

期望结果 #

已尝试代码（获取单条最新订单） #

select
   *
from
    Material m
join (
    select
        o.ORDERNO,
        max(o.ORDER_DATE) as ORDER_DATE,
        o.MAT1, 
        o.Menge
    from
        orders o
    group by
        o.ORDERNO,
        o.MAT1,
        o.Menge) as t1 on
    t1.MAT1 = m.NR

解决方案 #

在SQL Server中，使用窗口函数ROW_NUMBER()可以轻松实现分组取前N条数据的需求，具体代码如下：

SELECT 
    m.nr,
    m.price,
    m.weight,
    o.ORDERNO,
    o.ORDER_DATE,
    o.MAT1,
    o.Menge
FROM 
    materials m
INNER JOIN (
    SELECT 
        *,
        -- 按物料分组，按订单日期降序编号，每组内最新的订单排第1
        ROW_NUMBER() OVER (PARTITION BY MAT1 ORDER BY ORDER_DATE DESC) AS row_num
    FROM 
        orders
) o ON m.nr = o.MAT1
WHERE 
    o.row_num <= 3 -- 筛选每组内前3条最新订单
ORDER BY 
    m.nr, o.row_num; -- 按物料编号和行号排序，确保结果顺序符合预期

代码说明 #

1. 子查询中通过PARTITION BY MAT1将订单按物料分组，ORDER BY ORDER_DATE DESC让每组内的订单按日期从新到旧排序，ROW_NUMBER()为每组内的订单分配唯一的行号。
2. 关联materials表后，通过WHERE o.row_num <=3筛选出每组的前3条最新订单。
3. 最后按物料编号和行号排序，保证结果的展示顺序和期望一致。

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