加入两个表+第三个表作为条件列

Question

如果我有三个表，分别为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         | 
+--------------------------------------+-----------+----------+---------------------------------------+------------+----------------+--------------------------------------+ 

我不能没有在一个订单有多次失败的状态情况下，重复行做到这一点。

Answer 1

你试过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 

否则，我需要知道DBMS的SQL是，但每次我使用过的味道有一些方法来排序/顺序结果与某个分区，这样，你可以采取只基于第一条记录在一些关键，例如：

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或类似的东西，以获得最新的记录。

Answer 2

如果在履行表中没有一些可靠的方法来确定“最新状态”，例如时间戳，则需要选择一些任意方法以在可能的状态值中获得优先顺序。以下我在over clause中使用了case expression，因此如果某个订单的状态存在，则“成功”将获得行号1。如果您认为适合该列的其他可能值，请调整大小写表达式。

当包含行号的子查询连接到主查询时，连接包括and rn=1，因此每个订单只有履行行是可能的。

请注意，在示例数据中有一个缺少的仓库行，所以我不得不使用一个左连接，但我希望它是一个内连接在真正的分贝。

SQL Fiddle Demo

CREATE TABLE Warehouse 
    (ID varchar(36), Name varchar(9), Location varchar(5)) 
; 

INSERT INTO Warehouse 
    ("id", "name", "location") 
VALUES 
    ('9bcae08e-ad36-4d97-b9ec-4857714e902a', 'big', 'MLB'), 
    ('b442e783-4725-41e9-af83-f75004ee1b38', 'bigger', 'MLB'), 
    ('986d5aa9-0523-42d8-b183-dfd546d3e682', 'biggest', 'MLB') 
; 


CREATE TABLE Warehouse_order 
    (ID varchar(36), Warehouse_id varchar(36), type varchar(6), quantity int) 
; 

INSERT INTO Warehouse_order 
    ("id", "warehouse_id", "type", "quantity") 
VALUES 
    ('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) 
; 


CREATE TABLE Warehouse_fulfillment 
    (ID varchar(36), Order_id varchar(36), Status varchar(10)) 
; 


INSERT INTO Warehouse_fulfillment 
    ("id", "order_id", "status") 
VALUES 
    ('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') 
; 

查询1：

select 
    o.*, w.name, s.status, s.rn 
from Warehouse_order o 
left join Warehouse w on o.Warehouse_id = w.id 
left join (
     select id, order_id, status 
      , row_number() over(partition by order_id 
           order by case when status = 'SUCCESS' then 1 
              when status = 'FAILED' then 2 
              when status = 'REJECTED' then 3 
              else 4 end) as rn 
     from Warehouse_fulfillment 
    ) s on o.id = s.Order_id and rn=1 

Results：

|         id |       warehouse_id | type | quantity | name | status |  rn | 
|--------------------------------------|--------------------------------------|--------|----------|--------|---------|--------| 
| eceb0b5a-5afa-40e4-ac62-efb686e3bdae | 9bcae08e-ad36-4d97-b9ec-4857714e902a | BN_200 |  400 | big | (null) | (null) | 
| 9cb99fd9-9e5e-4240-8162-d28747be01cd | b442e783-4725-41e9-af83-f75004ee1b38 | BN_100 |  100 | bigger | SUCCESS |  1 | 
| 13370467-cf0c-47f2-8fea-a215500607e6 | 986d5aa9-0523-42d8-b183-dfd546d3e68 | BN_300 |  10 | (null) | FAILED |  1 | 

Answer 3

我不是这个完全肯定，但它听起来LIK Ë你只是想在order_fulfillment表左连接与“成功”状态的连接：

select 
    w.id, w.name, w.location, 
    o.id as order_id, o.type as order_type, 
    o.quantity as order_quantity, 
    f.id as fulfillment_id 
from 
    warehouse w 
    join warehouse_order o on 
    w.id = o.warehouse_id 
    left join warhouse_fulfillment f on 
    o.id = f.order_id and 
    f.status = 'SUCCESS' 

你似乎由于不关心非成功的记录，并有保证，有仅会成为'SUCCESS'的履行记录，这应该避免任何重复。