MySQL平均: Making Statement More Efficient

Question

我正在使用此 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'

Answer 1

这是一个标准的 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;