Sql查询（）

求一个Sql查询（在线等）
需求举例：
两个表：订单主表Order和明细表Detail，明细表中，每个商品对应一条记录，也就是说，Order表和Detail表是一对多的关系。

现在我要查询：所有购买商品A、B、C的订单（只包含A、B、C，且A、B、C都要包含）

请给个思路吧

[解决办法]

SQL code

select * from 订单主表Order where orderID in(     select orderID from (        select         r = row_number() over(partition by 商品ID order by 商品ID),        orderID, 商品ID         from 明细表Detail        where 商品ID in('A','B','C')        group by orderID, 商品ID    ) a where a.r=3)
[解决办法]
给点测试数据吧
[解决办法]
with D1 as(
select distinct orderid,ProductID from details
)
,D2 as (
select *
from D1 t1
where exists (select 1 from D1 where D1.ProductID=1)
and exists (select 1 from D1 where D1.ProductID=2)
and exists (select 1 from D1 where D1.ProductID=3)
and (select COUNT(*) from D1 where orderid=t1.orderid)=3
)
select * from [orders] where orderid in (select orderid from D2)
[解决办法]
楼主没有给产品表
SQL code
SELECT a.* FROM [Orders] a INNER JOIN [Details] b                         ON a.orderID=b.OrderID                        INNER JOIN [products] c                        ON b.productid=c.productid                        WHERE c.productName IN ('A','B','C')
[解决办法]
SQL code
;with ach as(    select a.orderid,sum(case when b.productid in (1101,1102) then 1 else 0 end) cnt,        count(b.id) tnum    from Orders a join Details b on a.orderid = b.orderid    group by a.orderid)select orderidfrom achwhere cnt = tnum and cnt = 2--A B C 就把in里面的改了，2改为3