我有兩張表,其中列很少:
一是為了SellOrders
OrderID (PK)| id_seller| id_product| placement_date
另一個是為了BuyOrders
OrderID (PK)| id_buyer| id_product| placement_date
帶有另一個關於客戶信息的表格Customer
(買家和賣家)
id_customer(PK) | name_customer
還有一張Product
桌子:
id(PK) | name_product
我想編寫一條 SQL 語句,placement_date
當為產品下達買單或賣單時選擇第一個 SQL 語句,以及對應的name_customer
+ the name_product
。
我寫了一個查詢,選擇相同的邏輯,但只針對賣方,我希望為買方和賣方選擇數據:
SELECT p.name_product, s.placement_date, c.name_customer
FROM Product p
OUTER APPLY (SELECT TOP 1 placement_date, id_seller
FROM Selling
WHERE id_product = p.id
ORDER BY placement_date, OrderID ASC) s
LEFT JOIN Customer c
ON c.id_customer = s.id_seller
從我所看到UNION SELECT
的似乎是這樣做的方法。我添加UNION
到OUTER APPLY
:
OUTER APPLY (SELECT TOP 1 placement_date, id_seller
FROM Selling
UNION
SELECT TOP 1 placement_date, id_buyer
WHERE id_product = p.id
ORDER BY placement_date, OrderID ASC) s
但我被困在LEFT JOIN
桌子上Customer
。有什麼幫助嗎?
如果我做對了,您首先需要購買/出售訂單的聯合
SELECT p.name_product, s.placement_date, c.name_customer
FROM Product p
OUTER APPLY (
SELECT TOP 1 placement_date, id_cust
FROM (
SELECT placement_date, OrderID, id_seller id_cust
FROM SellOrders
WHERE id_product = p.id
UNION
SELECT placement_date, OrderID, id_buyer
FROM BuyOrders
WHERE id_product = p.id
) t
ORDER BY placement_date, OrderID ASC
) s
LEFT JOIN Customer c
ON c.id_customer = s.id_cust
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句