問題描述

我有 3 張桌子:

• CP_carthead (idOrder)
• CP_cartrows (idOrder, idCartRow)
• CP_shipping(idCartRow、idShipping、dateShipped)

每個 idOrder 可以有多個 idCartRow.

There can be multiple idCartRows per idOrder.

我想獲取 CP_shipping 中存在 all idCartRows 的所有訂單.這看起來應該很簡單，但我在網上沒有找到太多.

I want to get all orders where all its idCartRows exist in CP_shipping. This seems like it should be simple, but I haven't found much on the web.

這是我現在的查詢:

SELECT
    s.idOrder
    , s.LatestDateShipped
FROM
    CP_carthead o
    LEFT OUTER JOIN (
                        SELECT
                            MAX(s.dateShipped) [LatestDateShipped]
                            , r.idOrder
                        FROM
                            CP_shipping s
                            LEFT OUTER JOIN CP_cartrows r ON s.idCartRow = r.idCartRow
                        GROUP BY
                            r.idOrder               
                    ) s ON o.idOrder = s.idOrder

推薦答案

您的查詢從s"返回行而不是訂單.根據你的問題，我想出了這個查詢:

Your query is returning rows from "s" and not the orders. Based on your question, I came up with this query:

select o.*
from CP_Carthead o
where o.orderId in (select cr.idOrder
                    from cp_cartrows cr left outer join
                         cp_shipping s
                         on cr.idCartRow = s.IdCartrow  
                    group by cr.idOrder
                    having count(s.idCartRow) = COUNT(*)
                   )

in 語句中的子查詢正在獲取所有購物車正在發貨的訂單.

The subquery in the in statement is getting orders all of whose cartrows are in shipping.

這篇關于僅當所有記錄都匹配時才加入 SQL的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！