MySQL Average：使语句更高效

发布于 2024-12-23 16:26:34 字数 513 浏览 1 评论 0原文

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

原文

I'm using this MySQL statement to find a neighborhood's average property assessment. The problem is that I have to add a SELECT statement for each neighborhood-- and there are a lot of neighborhoods. Is there a way to do it without having to specify 'RIVER FRONT' or 'OLD TOWN'? Is there a more efficient way?

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'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

找个人就嫁了吧 2024-12-30 16:26:34

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

This is an standard SQL aggregate statement and shows averages for all neighbourhoods
The left join allows you to get zero values where no property exists in a neighbourhood.

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;

回复收藏 0 原文

随风而去 2024-12-30 16:26:34

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;

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;

回复收藏 0 原文

~没有更多了~