表名:wu_plan
ID plan model corp_code plannum prixis
1 00001 exx22 nokia 2000 0
2 00002 lc001 sony 3000 0表名:wu_bom
ID plan pact amount
1 00001 aa1 300
2 00001 aa2 200
3 00002 bb1 500
4 00002 bb2 800
5 00002 bb3 400查询这两个表中plan唯一,每一个plan中,amount最少的,plannum大于prixis的记录
结果是:
ID plan model corp_code plannum prixis pact amount
1 00001 exx22 nokia 2000 0 a2 200
2 00002 lc001 sony 3000 0 bb3 400
ID plan model corp_code plannum prixis
1 00001 exx22 nokia 2000 0
2 00002 lc001 sony 3000 0表名:wu_bom
ID plan pact amount
1 00001 aa1 300
2 00001 aa2 200
3 00002 bb1 500
4 00002 bb2 800
5 00002 bb3 400查询这两个表中plan唯一,每一个plan中,amount最少的,plannum大于prixis的记录
结果是:
ID plan model corp_code plannum prixis pact amount
1 00001 exx22 nokia 2000 0 a2 200
2 00002 lc001 sony 3000 0 bb3 400
from wu_plan
left join
(
select [plan],pact,amount from wu_bom as tmp
where not exists(select 1 from wu_bom where [plan]=tmp.wu_bom and amount>tmp.amount
)as wu_bom on wu_plan.[plan]=wu_bom.[plan]
where plannum>prixis
from wu_plan t
inner join
(
select * from wu_bom tt
where not exists (select 1 from wu_bom vv where vv.plan=tt.plan and vv.amount<tt.amount)
) v
on t.plan=v.plan
where t.plannum>prixis
insert wu_plan select 1, '00001', 'exx22', 'nokia', 2000, 0
union all select 2, '00002', 'lc001', 'sony', 3000, 0
create table wu_bom(ID int,[plan] nvarchar(10),pact nvarchar(10),amount int)
insert wu_bom select 1, '00001', 'aa1', 300
union all select 2, '00001', 'aa2', 200
union all select 3 , '00002', 'bb1', 500
union all select 4, '00002', 'bb2', 800
union all select 5, '00002', 'bb3', 400select *
from wu_plan
left join
(
select [plan],pact,amount from wu_bom as tmp
where not exists(select 1 from wu_bom where [plan]=tmp.[plan] and amount<tmp.amount)
)as wu_bom on wu_plan.[plan]=wu_bom.[plan]
where plannum>prixis