如果OrderDetails表包含对OrderId和ProductId的唯一约束,则可以执行以下操作:
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
)
如果可以在同一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.Id
Where O.Id = 5