To: hyde100假设
P_ID P_NAME
-----------------
1 Test1
2 Test2O_ID O_P_ID O_NAME
------------------------------
1 1 Order1_01
2 1 Order1_02
3 1 Order1_03
4 2 Order2_01
5 2 Order2_02
6 2 Order2_03
7 2 Order2_04要求得到如下结果
P_ID P_NAME O_ID O_NAME
---------------------------------------
1 Test1 1 Order1_01
1 Test1 2 Order1_02
2 Test2 4 Order2_01
2 Test2 5 Order2_02也就是说每个产品最多取两条订单
P_ID P_NAME
-----------------
1 Test1
2 Test2O_ID O_P_ID O_NAME
------------------------------
1 1 Order1_01
2 1 Order1_02
3 1 Order1_03
4 2 Order2_01
5 2 Order2_02
6 2 Order2_03
7 2 Order2_04要求得到如下结果
P_ID P_NAME O_ID O_NAME
---------------------------------------
1 Test1 1 Order1_01
1 Test1 2 Order1_02
2 Test2 4 Order2_01
2 Test2 5 Order2_02也就是说每个产品最多取两条订单
我们一般采用如:select P_ID,P_NAME,O_ID,O_NAME from Product,Order where P_ID = O_P_ID;
但这样每个产品的所有订单都会被取出来,达不到期望的效果
select
o.P_ID,
p.P_NAME,
o.O_ID,
o.O_NAME
from [Order] as o
left join Product p on p.P_ID = o.O_P_ID
where o.O_ID in
(
select
top (2) O_ID
from [Order] as b
where b.O_P_ID =o.O_P_ID
order by O_NAME
)
declare @ta table(P_ID int, P_NAME varchar(10))
-----------------
insert @ta select 1, 'Test1'
insert @ta select 2, 'Test2' declare @tb table(O_ID int,O_P_ID int, O_NAME varchar(10))
------------------------------
insert @tb select 1 , 1, 'Order1_01'
insert @tb select 2 , 1 , 'Order1_02'
insert @tb select 3 , 1 , 'Order1_03'
insert @tb select 4 , 2 , 'Order2_01'
insert @tb select 5 , 2 , 'Order2_02'
insert @tb select 6 , 2 , 'Order2_03'
insert @tb select 7, 2 , 'Order2_04'
select * from (
select a.*,b.O_ID,b.O_Name from @ta a inner join @tb b on a.P_ID=b.O_P_ID)t
where checksum(*) in(select top 2 checksum(*) from (select a.*,b.O_ID,b.O_Name from @ta a inner join @tb b on a.P_ID=b.O_P_ID)s where t.P_ID=s.P_ID )
/*P_ID P_NAME O_ID O_Name
----------- ---------- ----------- ----------
1 Test1 1 Order1_01
1 Test1 2 Order1_02
2 Test2 4 Order2_01
2 Test2 5 Order2_02
*/
insert into product select 1,'Test1'
insert into product select 2,'Test2' create table [order](O_ID int,O_P_ID int,O_NAME varchar(10))
insert into [order] select 1,1,'order1_01'
insert into [order] select 2,1,'order1_02'
insert into [order] select 3,1,'order1_03'
insert into [order] select 4,2,'order2_01'
insert into [order] select 5,2,'order2_02'
insert into [order] select 6,2,'order2_03'
insert into [order] select 7,2,'order2_04'
go
select a.*,b.o_id,b.o_name from product a left join [order] b on a.p_id=b.o_p_id
where b.o_id in (
select top 2 o_id from [order] where o_p_id=b.o_p_id
)
go
drop table product,[order]
/*
P_ID P_NAME o_id o_name
----------- ---------- ----------- ----------
1 Test1 1 order1_01
1 Test1 2 order1_02
2 Test2 4 order2_01
2 Test2 5 order2_02(4 行受影响)
*/