求一个Sql查询（在线等）

[fisher_85]

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

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

请给个思路吧

[simonxt]

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
)

[开着拖拉机泡妞]

给点测试数据吧

[fisher_85]

[Quote=引用 2 楼 的回复:]

给点测试数据吧
[/Quote]

CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[TotalFee] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[Details](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [decimal](10, 2) NOT NULL,
[Quantity] [int] NOT NULL,
CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO Orders VALUES(100)
INSERT INTO Orders VALUES(100)
INSERT INTO Orders VALUES(100)
INSERT INTO Orders VALUES(250)
INSERT INTO Orders VALUES(250)


INSERT INTO Details VALUES(1, 1101, 60, 1)
INSERT INTO Details VALUES(1, 1102, 40, 1)

INSERT INTO Details VALUES(2, 1101, 60, 1)
INSERT INTO Details VALUES(2, 1102, 40, 1)

INSERT INTO Details VALUES(3, 2201, 20, 1)
INSERT INTO Details VALUES(3, 2202, 30, 1)
INSERT INTO Details VALUES(3, 2203, 50, 1)

INSERT INTO Details VALUES(4, 5501, 50, 1)
INSERT INTO Details VALUES(4, 5502, 100, 1)
INSERT INTO Details VALUES(4, 5503, 150, 1)

INSERT INTO Details VALUES(5, 1101, 60, 1)
INSERT INTO Details VALUES(5, 1102, 40, 1)
INSERT INTO Details VALUES(5, 5501, 50, 1)
INSERT INTO Details VALUES(5, 5502, 100, 1)

[fisher_85]

比如我要找购买商品1101和1102的订单，
实际满足条件的查询结果应该为订单1、2。
订单5包含其他商品5501、5502，所以不满足条件。

[fisher_85]

[Quote=引用 1 楼 的回复:]

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 明细表……
[/Quote]

hi，您提供的Sql不太满足我的需求~~~

[spiritofdragon]

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)

[迪迦凹凸曼]

楼主没有给产品表

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') 

[AcHerat]

;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

[fisher_85]

多谢各位
spiritofdragon和AcHerat给出的答案都能解决我的问题

[bobogenius]

AcHerat的SQL，个人感觉更好一点，非常强大，学习了