表1
ProID ProName
1 a
2 b 表2
ProID ProArea ProPric
1 BJ 10
1 SH 8
1 GZ 2
2 BJ 1
2 SH 3
2 GZ 1 我想得到每个产品的最低价格和最低价格所在地区以及产品信息,如有同一ProID有两条价格相等的最低价,则只显示一条:ID Name Area Pric
谢谢
ProID ProName
1 a
2 b 表2
ProID ProArea ProPric
1 BJ 10
1 SH 8
1 GZ 2
2 BJ 1
2 SH 3
2 GZ 1 我想得到每个产品的最低价格和最低价格所在地区以及产品信息,如有同一ProID有两条价格相等的最低价,则只显示一条:ID Name Area Pric
谢谢
Select table2.ProID as ID,ProName as Name,ProArea as Area, ProPric as Pric from table1, table2
Where ProPric in (Select min(ProPric) as Pric from table2 group by ProID) and table1.ProID=table2.ProID
insert temp1
select 1,'a' union all
select 2,'b' create table temp2(ProID int,ProArea char(4),ProPrice int)
insert temp2
select 1,'BJ',10 union all
select 1,'SH',8 union all
select 1,'SZ',2 union all
select 2,'BJ',1 union all
select 2,'SH',3 union all
select 2,'GZ',1 select t1.*,t2.ProArea,t2.ProPrice
from temp1 t1 join temp2 t2 on t1.proid=t2.proid
where t2.proprice=(select min(proprice) from temp2 where proid=t2.proid)drop table temp1,temp2/*--显示一条做不到
ProID Proname ProArea ProPrice
----------- ------- ------- -----------
1 a SZ 2
2 b BJ 1
2 b GZ 1
--*/