用于选择包含一组值的所有组的 SQL 语句 [英] SQL statement to select group containing all of a set of values

问题描述

在 SQL Server 2005 中，我有一个带有订单 ID 和产品 ID 的订单详细信息表.我想编写一个 sql 语句来查找包含特定订单中所有项目的所有订单.所以，如果订单 5 有项目 1、2 和 3，我希望所有其他订单也有 1、2 和 3.此外，如果订单 5 有 2 次和 3 次，我希望所有其他订单两个 2 和一个 3.

In SQL Server 2005, I have an order details table with an order id and a product id. I want to write a sql statement that finds all orders that have all the items within a particular order. So, if order 5 has items 1, 2, and 3, I would want all other orders that also have 1, 2, and 3. Also, if order 5 had 2 twice and 3 once, I'd want all other orders with two 2s and a 3.

我的偏好是它返回完全匹配的订单，但如果这样更容易/性能更好，那么超集的订单也是可以接受的.

My preference is that it return orders that match exactly, but orders that are a superset are acceptable if that's much easier / performs much better.

我尝试了如下所示的自联接，但发现订单包含任何项，而不是所有项.

I tried a self-join like the following, but that found orders with any of the items rather than all of the items.

SELECT * FROM Order O1
JOIN Order O2 ON (O1.ProductId = O2.ProductId)
WHERE O2.OrderId = 5

如果订单 5 两次包含相同的项目，这也会给我重复.

This also gave me duplicates if order 5 contained the same item twice.

推荐答案

如果 OrderDetails 表包含对 OrderId 和 ProductId 的唯一约束，那么您可以执行以下操作:

If the OrderDetails table contains a unique constraint on OrderId and ProductId, then you can do something like this:

Select ...
From Orders As O
Where Exists    (
                Select 1
                From OrderDetails As OD1
                Where OD1.ProductId In(1,2,3)
                    And OD1.OrderId = O.Id
                Group By OD1.OrderId
                Having Count(*) = 3
                )

如果可以在同一个订单上多次使用相同的 ProductId，那么您可以将 Have 子句更改为 Count(Distinct ProductId) = 3

If it is possible to have the same ProductId on the same Order multiple times, then you could change the Having clause to Count(Distinct ProductId) = 3

现在，鉴于上述情况，如果您想要每个订单具有相同签名和重复产品条目的情况，那就更棘手了.为此，您需要对相关产品的相关订单签名，然后查询该签名:

Now, given the above, if you want the situation where each order has the same signature with duplicate product entries, that is trickier. To do that you would need the signature of order in question over the products in question and then query for that signature:

With OrderSignatures As
    (
    Select O1.Id
        ,   (
            Select '|' + Cast(OD1.ProductId As varchar(10))
            From OrderDetails As OD1
            Where OD1.OrderId = O1.Id
            Order By OD1.ProductId
            For Xml Path('')
            ) As Signature
    From Orders As O1
    )
Select ...
From OrderSignatures As O
    Join OrderSignatures As O2
        On O2.Signature = O.Signature
            And O2.Id <> O.Id
Where O.Id = 5

这篇关于用于选择包含一组值的所有组的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！