连接两个表+第三个表作为条件列

ke3pup 发表于 Dev

ke3pup

如果我有三张表叫warehouse，warehouse_order表叫warehouse_fulfillment。仓单由仓库管理员创建，最初有 0 条履约记录，可以有许多warehouse_fulfillment状态为失败/拒绝状态的记录，并且只有一个成功状态（已完成）：

-- Warehouse
+---------------------------------------+-----------+----------+
| id                                    | name      | location |
+---------------------------------------+-----------+----------+
| 9bcae08e-ad36-4d97-b9ec-4857714e902a  | "big"     | "MLB"    |
+---------------------------------------+-----------+----------+
| b442e783-4725-41e9-af83-f75004ee1b38  | "bigger"  | "MLB"    |
+---------------------------------------+-----------+----------+
| 986d5aa9-0523-42d8-b183-dfd546d3e682  | "biggest" | "MLB"    |
+---------------------------------------+-----------+----------+


-- Warehouse_order Table
+---------------------------------------+--------------------------------------+--------+----------+
| id                                    | warehouse_id                         |  type  | quantity |
+---------------------------------------+--------------------------------------+--------+----------+
| 9cb99fd9-9e5e-4240-8162-d28747be01cd  | b442e783-4725-41e9-af83-f75004ee1b38 | BN_100 | 100      |
+---------------------------------------+-------------------------------------+--------+-----------+
| eceb0b5a-5afa-40e4-ac62-efb686e3bdae  | 9bcae08e-ad36-4d97-b9ec-4857714e902a | BN_200 | 400      |
+---------------------------------------+--------------------------------------+--------+----------+
| 13370467-cf0c-47f2-8fea-a215500607e6  | 986d5aa9-0523-42d8-b183-dfd546d3e68  | BN_300 | 10       |
+---------------------------------------+--------------------------------------+--------+----------+


-- Warhouse_fulfillment Table
+---------------------------------------+---------------------------------------+------------+
| id                                    | order_id                              | status     |
+---------------------------------------+---------------------------------------+------------+
| 8a69edde-2346-48b8-96d0-6c4e25527f38  | 9cb99fd9-9e5e-4240-8162-d28747be01cd  | "FAILLED"  |
+---------------------------------------+---------------------------------------+------------+
| a2006a64-9bdc-4bfa-ba14-a44769aeb4a2  | 9cb99fd9-9e5e-4240-8162-d28747be01cd  | "REJECTED" |
+---------------------------------------+---------------------------------------+------------+
| bf0aa1fc-6dfc-4fd0-ba20-be101b1985d1  | 9cb99fd9-9e5e-4240-8162-d28747be01cd  | "FAILED"   |
+---------------------------------------+---------------------------------------+------------+
| 48c7d747-2f9b-4535-8f27-210a43cf5c30  | 9cb99fd9-9e5e-4240-8162-d28747be01cd  | "SUCCESS"  |
+---------------------------------------+---------------------------------------+------------+
| 7f8e18c9-4322-428a-9370-9ecd1c5ef286 |  13370467-cf0c-47f2-8fea-a215500607e6  | "FAILED"   |
+---------------------------------------+---------------------------------------+------------+

我想以结果如下所示的方式查询上述记录：

+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+
| id                                   | name      | location | order_id                              | order_type | order_quantity | fulfillment_id                       |
+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+
| 9bcae08e-ad36-4d97-b9ec-4857714e902a | "big"     | "MLB"    | eceb0b5a-5afa-40e4-ac62-efb686e3bdae  | "BN_100"   | 100            | NULL                                 |
+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+
| b442e783-4725-41e9-af83-f75004ee1b38 | "bigger"  | "MLB"    | 9cb99fd9-9e5e-4240-8162-d28747be01cd  | "BN_200"   | 400            | 48c7d747-2f9b-4535-8f27-210a43cf5c30 |
+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+
| 986d5aa9-0523-42d8-b183-dfd546d3e682 | "biggest" | "MLB"    | 13370467-cf0c-47f2-8fea-a215500607e6  | "BN_300"   | 10             | NULL                                 |
+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+

在订单有多个失败状态的情况下，如果没有重复的行，我就无法做到这一点。

斯蒂芬妮

你试过 SELECT DISTINCT 吗？因为您在选择列表中没有状态列（导致重复），所以这应该有效。

SELECT DISTINCT W.id, W.name, W.location, WO.id order_id, WO.type order_type, WO.quantity order_quantity, WF.id fulfillment_id
FROM warehouse W
     LEFT JOIN warehouse_order WO ON W.id = WO.warehouse_id
     LEFT JOIN warehouse_fulfillment WF on WF.order_id = WO.id

否则，我需要知道 SQL 是用于什么 DBMS 的，但是我使用过的每种风格都有某种方法可以对分区的结果进行排名/排序，以便您可以根据某个键仅获取第一条记录，例如：

SELECT id, name, location, order_id, order_type, order_quantity
FROM (
   SELECT W.id, W.name, W.location, WO.id order_id, WO.type order_type, WO.quantity order_quantity, WF.id fulfillment_id, ROW_NUMBER() OVER (PARTITION BY WO.id ORDER BY  WF.ID) rNum
FROM warehouse W
     LEFT JOIN warehouse_order WO ON W.id = WO.warehouse_id
     LEFT JOIN warehouse_fulfillment WF on WF.order_id = WO.id
 ) A
 WHERE rNum = 1

最好按日期 DESC 或类似方式订购以获得最新记录。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。