我有這樣的桌子
id | value | date | for | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
16 | 48.0 | 2021-02-21 09:00:00 | Chest | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm
13 | 49.0 | 2021-02-06 10:00:00 | Chest | cm
25 | 49.0 | 2021-02-06 10:00:00 | Hip | cm
12 | 48.0 | 2021-02-05 10:00:00 | Chest | cm
結果集應回傳：
id | value | date | for | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm
此輸出包含“for”列中每個不同值的 2 行，按日期降序排序
uj5u.com熱心網友回復：
使用視窗函式很容易做到，比如row_number()對每個組進行排名，然后只取前兩個：
WITH ranked AS
(SELECT id, value, date, for, unit,
row_number() OVER (PARTITION BY for ORDER BY date DESC) AS rn
FROM mytable)
SELECT id, value, date, for, unit
FROM ranked
WHERE rn <= 2
ORDER BY for, date DESC;
給
id value date for unit
-- ----- ------------------- ----- ----
17 49.0 2021-02-22 10:00:00 Chest cm
14 49.0 2021-02-21 10:00:00 Chest cm
29 49.0 2021-02-22 10:00:00 Hip cm
28 48.0 2021-02-21 10:00:00 Hip cm
26 49.0 2021-02-21 10:00:00 Waist cm
27 48.0 2021-02-20 10:00:00 Waist cm
為獲得最佳結果，請添加一個索引mytable(for, date DESC)是否會頻繁運行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/490819.html
標籤：sqlite