SQLite中通过INNER JOIN关联表获取每日最高温并将ID替换为实际地理位置坐标

Absolutely! Using an INNER JOIN is exactly the right solution to map the location_id from your temperatures table to the actual geographic coordinates stored in the locations table. Here's how to adjust your query to get the result you want:

Updated SQL Query #

SELECT 
    l.location,
    t.day,
    MAX(t.temperature) AS max_temperature
FROM temperatures t
INNER JOIN locations l 
    ON t.location_id = l.id
-- Add this WHERE clause to filter for the specific ID passed in
WHERE l.id = <your_input_id>
GROUP BY l.location, t.day
ORDER BY t.day;

Breakdown of the Changes: #

• INNER JOIN: This links the two tables using the shared ID field (temperatures.location_id ↔ locations.id), letting us pull in the location coordinate value instead of just the numeric ID.
• Alias Names: Using t for temperatures and l for locations keeps the query clean and easy to read.
• Grouping Adjustment: Since we're now selecting l.location instead of location_id, we need to include l.location in the GROUP BY clause along with t.day to correctly calculate the daily maximum temperature per location.
• Filtering: The WHERE clause lets you target the specific location ID passed via the URL, so you only get results for that single location.

Notes: #

• If the input ID doesn't exist in the locations table, INNER JOIN will return no results (which makes sense, since there's no matching coordinate data). If you ever need to handle cases where the ID might be invalid (and still return temperature data with a null coordinate), you could switch to a LEFT JOIN—but for your stated use case, INNER JOIN is the most appropriate choice.

内容的提问来源于stack exchange，提问作者Ntavass