使用子查询从右表中获取最新记录

-- orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- order_details 表
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    detail_date DATE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

SELECT od.*
FROM order_details od
WHERE od.detail_date = (
    SELECT MAX(od2.detail_date)
    FROM order_details od2
    WHERE od2.order_id = od.order_id
);

SELECT od.*
FROM order_details od
JOIN (
    SELECT order_id, MAX(detail_date) AS max_detail_date
    FROM order_details
    GROUP BY order_id
) od_max ON od.order_id = od_max.order_id AND od.detail_date = od_max.max_detail_date;

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY detail_date DESC) AS rn
    FROM order_details
) t
WHERE rn = 1;