我有一個資料庫，其中存盤了兩個位置的一些預測。每個位置都有 3 天的預報，每天每小時都有多個預報。我想每天為每個位置選擇最新的預報。位置保存為“位置”，每一天都保存為“applicable_date”，每天都有一個預測，每小時保存為“創建”。我正在嘗試這樣的事情，但不起作用：
SELECT * FROM (
SELECT * FROM `forecast` GROUP BY location
) GROUP BY applicable_date
ORDER BY created DESC
LIMIT 1
uj5u.com熱心網友回復：
這樣的事情應該沒問題。這些回傳每一天/位置的整個預測行。
對于 8.0 版本：
WITH cte AS (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY location, applicable_date ORDER BY created DESC ) AS rn
FROM forecast
)
SELECT *
FROM cte
WHERE rn = 1
;
對于 8.0 及之前版本：
SELECT f.*
FROM (
SELECT location, applicable_date
, MAX(created) AS max_created
FROM forecast
GROUP BY location, applicable_date
) AS cte
JOIN forecast AS f
ON f.location = cte.location
AND f.applicable_date = cte.applicable_date
AND f.created = cte.max_created
;
uj5u.com熱心網友回復：
SELECT location,
CAST(created AS DATE) `date`,
-- or applicable_date `date`,
MAX(created) last_datetime
FROM forecast
GROUP BY 1, 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381357.html
標籤：mysql sql 选择 通过...分组 sql-order-by
上一篇：格式間隔日期型別作為PostgreSQL函式中的引數
下一篇：如何將列的結果連接到新列