SQLite多个案例当奇怪的结果

Question

我使用多个CASE WHEN来查找所选日期内的设备操作，但有时我不会只获取设备名称(如V或C(，而是获取完整的操作名称。如果我试图用ELSE"替换"ELSE操作"，我会得到一些空白，即使没有任何空白操作。。。如何改进我的查询？

SELECT device,
CASE 
WHEN action='Vaccum' AND strftime('%d', timestamp_action) = '25' THEN 'V' 
WHEN action='Cooling' AND strftime('%d', timestamp_action) = '25' THEN 'C' ELSE action END AS '25',

CASE 
WHEN action='Vaccum' AND strftime('%d', timestamp_action) = '26' THEN 'V' 
WHEN action='Cooling' AND strftime('%d', timestamp_action) = '26' THEN 'C' ELSE action END AS '26',

FROM diary WHERE strftime('%m', timestamp_action = '08')
GROUP BY device
ORDER BY device

我想在所有设备的选定日期获取最新操作。我有大约100台设备，我需要整个月的操作。

示例表：

timestamp_action    |   device      |   action
------------------------+---------------+-----------
2022-08-25 11:08    |      1        |   Cooling
2022-08-25 11:09    |      1        |   Vaccum
2022-08-25 11:08    |      2        |   Cooling
2022-08-26 11:10    |      2        |   Vaccum
2022-08-26 11:11    |      2        |   Cooling
2022-08-26 12:30    |      1        |   Vaccum

所以我要寻找的结果是：

device |    25     | 26 .....
-----------+-----------+--------------
1      |    V      |  V
2      |    C      |  C

Answer 1

使用两个聚合级别：

WITH cte AS (
SELECT device, 
strftime('%d', timestamp_action) day,
CASE action WHEN 'Vaccum' THEN 'V' WHEN 'Cooling' THEN 'C' ELSE action END action,
MAX(timestamp_action) max_timestamp_action
FROM diary
WHERE strftime('%Y-%m', timestamp_action) = '2022-08'
GROUP BY device, day
)  
SELECT device,
MAX(CASE WHEN day = '25' THEN action END) `25`,
MAX(CASE WHEN day = '26' THEN action END) `26`
FROM cte
GROUP BY device;

请参阅演示