SQL语句选择包含所有一组值的组

如果OrderDetails表包含对OrderId和ProductId的唯一约束，则可以执行以下操作：

Select ...From Orders As OWhere 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     )

如果可以在同一Order上多次使用相同的ProductId，则可以将Haveing子句更改为

Count(Distinct ProductId) = 3

现在，根据以上所述，如果您希望每个订单都具有相同的签名并带有重复的产品条目，那就比较麻烦了。为此，您需要有关产品的相关订单签名，然后查询该签名：

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.IdWhere O.Id = 5