时间戳

首页 > 解决方案 > 如何获取涉及三个SQL表的最旧数据

sql - 如何获取涉及三个SQL表的最旧数据

问题描述

我有三个表，Items，Orders和一个名为ItemOrders的多对多链接表。每个项目可以有很多订单，当然一个订单可以有很多项目。

我需要一个返回的项目列表：

但仅适用于每个项目的最早订单。我已经尝试了各种方式的连接和子查询，但就是无法理解如何做到这一点。

例如，项目：

+--------+---------------+
| ItemId |   ItemName    |
+--------+---------------+
|     1  | Crisps        |
|     2  | Chocolate Bar |
+--------+---------------+

项目订单：

+--------+---------+
| ItemId | OrderId |
+--------+---------+
|      1 |      21 |
|      1 |      22 |
|      2 |      23 |
|      2 |      24 |
|      2 |      22 |
+--------+---------+

和订单：

+---------+------------+---------+
| OrderId | OrderDate  |  Name   |
+---------+------------+---------+
|      21 | 2019-03-12 | ORD2501 |
|      22 | 2019-03-20 | ORD2502 |
|      23 | 2019-03-28 | ORD2503 |
|      24 | 2019-03-31 | ORD2504 |
+---------+------------+---------+

我试图得到这个：

+---------------+-----------+
|   ItemName    | OrderName |
+---------------+-----------+
| Crisps        | ORD2501   |
| Chocolate Bar | ORD2502   |
+---------------+-----------+

任何有关正确方法的帮助将不胜感激。这适用于 MS SQL Server。

标签： sqlsql-server

解决方案

一种方法是在其中CROSS APPLY使用TOP (1)and ORDER BY：

SELECT I.itemName,
       OD.OrderId
FROM dbo.Items I
     CROSS APPLY (SELECT TOP (1) O.[Name]
                  FROM dbo.ItemOrders OI
                       JOIN dbo.Orders O ON OI.OrderID = O.OrderID
                  WHERE OI.ItemID = I.ItemID
                  ORDER BY O.OrderDate DESC) OD;

另一种方法是使用TOP (1) WITH TIESand ROW_NUMBER：

SELECT TOP (1) WITH TIES
       I.ItemName,
       O.[Name]
FROM dbo.Items I
     JOIN dbo.ItemOrders OI ON OI.ItemID = I.ItemID
     JOIN dbo.Orders O ON OI.OrderID = O.OrderID
ORDER BY ROW_NUMBER() OVER (PARTITION BY I.ItemID ORDER BY O.OrderDate DESC);

或者你可以放入ROW_NUMBER一个 CTE，然后使用 aWHERE来过滤：

WITH RN AS(
    SELECT I.ItemName,
           O.[Name],
           ROW_NUMBER() OVER (PARTITION BY I.ItemID ORDER BY O.OrderDate DESC) AS RN
    FROM dbo.Items I
         JOIN dbo.ItemOrders OI ON OI.ItemID = I.ItemID
         JOIN dbo.Orders O ON OI.OrderID = O.OrderID)
SELECT ItemName,
       [Name]
FROM RNs
WHERE RN = 1;

推荐阅读