这个表,是全部包含,还是只要有一个即可?
insert sale_item values (10003,'P0001',4 , 2700.00,'1996-10-15')
insert sale_item values (10003,'P0004',2 , 1580.00,'1996-10-15')
insert sale_item values (10003,'P0001',4 , 2700.00,'1996-10-15')
insert sale_item values (10003,'P0004',2 , 1580.00,'1996-10-15')
(order_no int Not null,
prod_id char(5) Not null,
constraint PK_tb primary key(order_no,prod_id),
qty int Not null,
unit_price numeric(7,2) Not null,
order_date datetime null
)
go
insert tb values (10001,'P0001',5 , 2500.00,'1996-10-22')
insert tb values (10001,'P0002',3 , 6500.00,'1996-10-22')
insert tb values (10001,'P0003',2 , 5300.00,'1996-10-22')
insert tb values (10001,'P0004',2 , 1600.00,'1996-10-22')
insert tb values (10002,'P0001',3 , 2600.00,'1996-11-10')
insert tb values (10002,'P0003',1 , 5300.00,'1996-11-10')
insert tb values (10002,'P0008',2 , 4800.00,'1996-11-10')
insert tb values (10003,'P0001',4 , 2700.00,'1996-10-15')
insert tb values (10003,'P0004',2 , 1580.00,'1996-10-15')--只包含其中任何一个。
select distinct order_no from tb where prod_id in (select distinct prod_id from tb where order_no = 10003) and order_no <> '10003'
/*
order_no
-----------
10001
10002(所影响的行数为 2 行)
*/--必须包含所有的prod_id
select distinct order_no from tb where prod_id in (select distinct prod_id from tb where order_no = 10003) and order_no <> '10003'
group by order_no having count(*) >= (select count(distinct prod_id) from tb where order_no = 10003)
/*
order_no
-----------
10001(所影响的行数为 1 行)
*/drop table tb
--第1记录集:订单主表
Select a.*,b.cust_name
From sales As a
Inner Join customer As b On b.cust_id=a.cust_id
Where a.order_no=@order_no--第2记录集:订单明细
Select a.*,c.prod_name
From sale_item As a
Inner Join sales As b On b.order_no=a.order_no
Inner Join product As c On c.prod_id=a.prod_id
Where b.order_no=@order_no/*
order_no cust_id sale_id tot_amt order_date invoice_no cust_name
----------- ------- ------- --------------------------------------- ----------------------- ---------- --------------------
10003 C0003 E0014 13960.00 1996-10-15 00:00:00.000 I000000003 客户丙order_no prod_id qty unit_price order_date prod_name
----------- ------- ----------- --------------------------------------- ----------------------- --------------------
10003 P0001 4 2700.00 1996-10-15 00:00:00.000 16M DRAM
10003 P0004 2 1580.00 1996-10-15 00:00:00.000 3.5寸软驱*/
select distinct prod_id from sale_item where order_no='10003'
)
/*
order_no
-----------
10001
10002
10003(3 行受影响)
*/