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 orderid
from ach
where cnt = tnum and cnt = 2

--

A B C 就把in里面的改了，2改为3