我有这个查询,效果很好.我使用MIN(home_price)显示作为起始价格,我使用此查询为api和WHERE子句添加到它,所以如果我按价格搜索MIN(home_price)更改.
SELECT MIN(home_price) as min_home_price,
id,
name,
community,
maplocation,
locationLabel,
logo
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
INNER JOIN rb_locations
ON readyBuilt.home_location = rb_locations.locationId
WHERE id IN ( SELECT DISTINCT id
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
WHERE isDeleted = 0 AND is_upcoming = 0)
AND home_status = 1
GROUP BY id,name,community,mapLocation,locationLabel,logo
ORDER BY name
所以我的解决方案是使用子查询
SELECT id,
name,
community,
maplocation,
locationLabel,
logo,
(SELECT MIN(home_price) as min_home_price
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
INNER JOIN rb_locations
ON readyBuilt.home_location = rb_locations.locationId
WHERE id IN ( SELECT DISTINCT id
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
WHERE isDeleted = 0
AND is_upcoming = 0)
AND home_status = 1
GROUP BY id,name,community,mapLocation,locationLabel,logo
ORDER BY name) as org_min_home_price
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
INNER JOIN rb_locations
ON readyBuilt.home_location = rb_locations.locationId
WHERE id IN ( SELECT DISTINCT id
FROM ourCommunity
INNER JOIN readyBuilt
ON community = home_community
WHERE isDeleted = 0 AND is_upcoming = 0)
AND home_status = 1
GROUP BY id,name,community,mapLocation,locationLabel,logo
ORDER BY name
但是当我执行第二个查询时,我收到此错误
Subquery returns more than 1 row
当我删除GROUP BY时,MIN(home_price)没有错误,每行都是相同的.有没有人对如何完成我想要完成的事情有任何建议?