有两个表:
供应商
======================================================= SUPID SNAME RATING LOCATION======================================================= 100 Smith 20 Sydney 110 Parkington 30 Melbourne 120 Golden Valley 30 Ballarat 经销商
======================================================= MID MNAME PRICE SUPID======================================================= 500 Colour TV 400 100 510 VCR 300 110 520 Hi-Fi 600 110 530 VCR 290 120请问怎样列出所有供应商的姓名(无重复),条件为:价格在200以上
供应商
======================================================= SUPID SNAME RATING LOCATION======================================================= 100 Smith 20 Sydney 110 Parkington 30 Melbourne 120 Golden Valley 30 Ballarat 经销商
======================================================= MID MNAME PRICE SUPID======================================================= 500 Colour TV 400 100 510 VCR 300 110 520 Hi-Fi 600 110 530 VCR 290 120请问怎样列出所有供应商的姓名(无重复),条件为:价格在200以上
where exisits (select 1 from 经销商 b where a.supid = b.supid and b.price > 200 )
where exisits (select 1 from 经销商 b where a.supid = b.supid and b.price > 200 )
这样写是正确的。但是好像没有必要用distinct
select 100 supid,'Smith' sname,20 rating,'Sydney' location from dual
union all
select 110 supid,'Parkington' sname,30 rating,'Melbourne' location from dual
union all
select 120 supid,'Golden Valley' sname,30 rating,'Ballarat' location from dual
),b as (
select 500 MID,'Colour TV' MNAME,400 price,100 SUPID from dual
union all
select 510 MID,'VCR' MNAME,300 price,110 SUPID from dual
union all
select 520 MID,'Hi-Fi' MNAME,600 price,110 SUPID from dual
union all
select 530 MID,'VCR' MNAME,290 price,120 SUPID from dual
)
select distinct sname from a where supid in (
select supid from b where price >200
)