我使用这个MySQL语句来查找邻居的平均财产评估。问题是,我必须为每个社区添加一个SELECT语句——而且有很多社区。有没有一种不需要指定"RIVER FRONT"或"OLD TOWN"的方法?有没有更有效的方法?
SELECT AVG(property_table.assessment)
FROM property_table, neighborhood_table
WHERE property_table.id = neighborhood_table.id AND neighborhood_table.neighborhood = 'RIVER FRONT'
UNION ALL
SELECT AVG(property_table.assessment)
FROM property_table, neighborhood_table
WHERE property_table.id = neighborhood_table.id AND neighborhood_table.neighborhood = 'OLD TOWN'
这是一个标准的SQL聚合语句,显示了所有邻域的平均值
左连接允许您在邻域中不存在属性时获得零值。
SELECT
n.neighborhood, COALESCE(AVG(p.assessment), 0)
FROM
neighborhood_table n
LEFT JOIN
property_table p ON p.id = n.id
GROUP BY
n.neighborhood;
SELECT AVG(property_table.assessment), neighborhood_table.neighborhood
FROM property_table
INNER JOIN neighborhood_table ON property_table.id = neighborhood_table.id
WHERE neighborhood_table.neighborhood IN ('RIVER_FRONT', 'OLD TOWN')
GROUP BY neighborhood_table.neighborhood;