如何在三个表上使用MAX函数？
所以，我有一个SQL查询的问题。
这是关于获取德国城市的天气数据。我有4个表: staedte (具有主键loc_id的城市)、gehoert_zu (包含城市键和离这个城市最近的气象站的键(stations_id))、wettermessung (包含所有天气信息和站的键值)和wetterstation (包含站的键和位置)。我使用的是PostgreSQL
下面是表格的外观：
wetterstation
s_id[PK] standort lon lat hoehe
----------------------------------------
10224 Bremen 53.05 8.8 4
wettermessung
stations_id[PK] datum[PK] max_temp_2m ......
----------------------------------------------------
10224 2013-3-24 -0.4
staedte
loc_id[PK] name lat lon
-------------------------------
15 Asch 48.4 9.8
gehoert_zu
loc_id[PK] stations_id[PK]
-----------------------------
15 10224我要做的是获取指定日期(例如)具有最高温度的城市的名称(可以是整个月，也可以是一天)。因为天气数据绑定到一个站点，所以我实际上需要获取站点的ID，然后只需选择一个与该站点对应的城市。一个可能的问题是：“6月份哪个城市最热?”比方说，测得的最高温度是在10224号站。因此，我想要获取城市Asch。到目前为止，我得到的是这个
SELECT name, MAX (max_temp_2m)
FROM wettermessung, staedte, gehoert_zu
WHERE wettermessung.stations_id = gehoert_zu.stations_id
AND gehoert_zu.loc_id = staedte.loc_id
AND wettermessung.datum BETWEEN '2012-8-1' AND '2012-12-1'
GROUP BY name
ORDER BY MAX (max_temp_2m) DESC
LIMIT 1结果有两个问题: 1)花费的时间太长了。表不是那么大(cities有大约70k个条目)，但它需要1到7分钟来完成任务(取决于时间跨度) 2)它总是生成相同的城市，我非常确定它也不是正确的城市。
我希望我能把我的问题解释得足够清楚，我会很高兴得到任何形式的帮助。提前感谢！:D
回答 3
Stack Overflow用户
发布于 2013-06-27 07:08:10
如果您想要获取每个城市的最高温度，请使用以下语句：
SELECT * FROM (
SELECT gz.loc_id, MAX(max_temp_2m) as temperature
FROM wettermessung as wm
INNER JOIN gehoert_zu as gz
ON wm.stations_id = gz.stations_id
WHERE wm.datum BETWEEN '2012-8-1' AND '2012-12-1'
GROUP BY gz.loc_id) as subselect
INNER JOIN staedte as std
ON std.loc_id = subselect.loc_id
ORDER BY subselect.temperature DESC使用以下语句获取温度最高的城市(只有1个城市)：
SELECT * FROM(
SELECT name, MAX(max_temp_2m) as temp
FROM wettermessung as wm
INNER JOIN gehoert_zu as gz
ON wm.stations_id = gz.stations_id
INNER JOIN staedte as std
ON gz.loc_id = std.loc_id
WHERE wm.datum BETWEEN '2012-8-1' AND '2012-12-1'
GROUP BY name
ORDER BY MAX(max_temp_2m) DESC
LIMIT 1) as subselect
ORDER BY temp desc
LIMIT 1出于性能原因，请始终使用显式连接作为LEFT、RIGHT、INNER JOIN，并避免使用带有分隔表名的连接，这样您的sql serevr就不必猜测您的表引用。
Stack Overflow用户
发布于 2013-06-27 06:56:20
这是一个关于如何获得最高、最低、最大、最小值的项目的一般示例。您可以根据您的特定情况调整它。
select fred, barney, wilma
from bedrock join
(select fred, max(dino) maxdino
from bedrock
where whatever
group by fred ) flinstone on bedrock.fred = flinstone.fred
where dino = maxdino
and other conditionsStack Overflow用户
发布于 2013-06-27 08:26:04
我建议您使用一致的命名约定。单数术语适用于每行只包含一项的表，这是一个很好的约定。你只会打破桌子这是staedte。应为stadt。
我建议始终使用station_id，而不是s_id或stations_id。
以这些前提为基础，回答您的问题：
...获取城市的名称，使用...指定日期的最高温度
SELECT s.name, w.max_temp_2m
FROM (
SELECT station_id, max_temp_2m
FROM wettermessung
WHERE datum >= '2012-8-1'::date
AND datum < '2012-12-1'::date -- exclude upper border
ORDER BY max_temp_2m DESC, station_id -- id as tie breaker
LIMIT 1
) w
JOIN gehoert_zu g USING (station_id) -- assuming normalized names
JOIN stadt s USING (loc_id)- 使用显式的
JOIN条件以获得更好的可读性，并使用表别名来简化查询。- 使用
x >= a AND x < b来包括下边界和排除上边界，这是常用的case. - Aggregate first，并选择温度最高的电台，然后再连接到其它表以检索城市名称。更简单，更快。
- 你没有指定在给定的时间范围内，当多个"wettermessungen“在
max_temp_2m上绑定时该怎么做。我添加了station_id作为决胜局，这意味着如果有多个符合条件的电台，id最低的电台将被一致地挑选。
- 使用
https://stackoverflow.com/questions/17331639
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号