我在MySQL数据库中有2个表，这些表定期附加新数据。一个是出租物业及其特征的列表，例如2床，2浴室，位置，出租价格等。第二个表是当前待售物业的列表。从租金表中，我可以使用查询来确定特定类型的房地产在特定位置获得的平均租金：
SELECT bed, bath, type, suburb, postcode, AVG(price)
FROM rent_suburbs
GROUP BY bed, bath, type, suburb, postcode
我希望能够从buy_items表中选择属性，其中用户定义的销售价格百分比小于匹配类型和位置的属性的平均租金。
我正在尝试将代码修正为有人建议的以下代码，但我被卡住了。
select listing, bed, bath, type, address, postcode, state, price
from
buy_items
where (price*.0014) < avg(price) from
select
bed, bath, type, suburb, postcode, avg(price)
from
rent_items
group by bed , bath , type , suburb , postcode
/* and bed, bath, type, suburb and postcode match the buy_items property ????
我是新来的，因此不胜感激。谢谢
表结构如下：
buy_items
buy_items_id2 int(11)
car int(11)
price int(11)
listing varchar(12)
bed int(11)
bath int(11)
suburb varchar(25)
state int(11)
scrapedate int(11)
address varchar(45)
type varchar(25)
postcode int(11)
和
rent_items
rent_items_id2 int(11)
car int(11)
price int(11)
listing int(11)
bed int(11)
bath int(11)
suburb varchar(25)
state varchar(5)
scrapedate int(11)
address varchar(45)
type varchar(25)
postcode int(11)
试试这个：
select listing, b.bed, bath, type, address, postcode, state, price
from
buy_items b
join (
SELECT bed, bath, type, suburb, postcode, AVG(price) as avg_price
FROM rent_suburbs
GROUP BY bed, bath, type, suburb, postcode ) a
on a.bed=b.bed and a.bath=b.bath and a.suburb=b.suburb and a.postcode=b.postcode
where (b.price*.0014) < a.avg_price;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句