CREATE TABLE nearest_location(
created_date TIMESTAMP,
device VARCHAR(255),
rake_device VARCHAR(255),
is_same_location INT,
rounded_geo_lat FLOAT,
rounded_geo_lng FLOAT,
idle_or_moving VARCHAR(255),
time_diff_idle_vs_moving INT,
max_idle INT,
row_num INT
);

INSERT INTO nearest_location(created_date, device, rake_device, is_same_location, 
rounded_geo_lat, rounded_geo_lng, idle_or_moving, time_diff_idle_vs_moving, max_idle, 
row_idle)
VALUES
('2023-12-26 09:58', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 3, 31, 1),
('2023-12-26 09:55', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 19, 16, 
2),
('2023-12-26 09:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 10, 
3),
('2023-12-26 08:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 5, 4),
('2023-12-26 07:36', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.78, 69.67, 'moving', 69, 2, 
5),
('2023-12-26 06:27', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.77, 69.67, 'idle', 18, 4, 6),
('2023-12-26 06:09', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.77, 69.67, 'moving', 12, 61, 
7),
('2023-12-26 05:57', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.67, 'idle', 17, 3, 8),
('2023-12-26 05:40', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.74, 69.67, 'moving', 11, 60, 
9),
('2023-12-26 05:29', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.68, 'idle', 44, 1, 
10);

字符串
上面的数据包含2列称为max_idle和row_num，所以无论row_num = 1，我想创建新的列，并附加值的rounded_geo_lat，rounded_geo_lat的max_idle=1，所以新的模式看起来像这样。这是我正在寻找的输出：
| 创建日期|装置|耙装置|is_same_location|圆形地理纬度|圆形土工格栅|空转或运动|怠速与移动时间差|最大怠速|rn_idle| max_idle_lat1| max_idle_lng1| time_diff1| max_idle_lat2| max_idle_lng2| time_diff2|
| --|--|--|--|--|--|--|--|--|--|--|--|--|--|--|--|
| 2019 - 05 - 21 10：00：00| SLA11502| TXDP-12-SLA11502| 1 |二十点四十七|八十二点九|空闲| 23 | 10 | 1 |二十一点三十一分|84.1| 61 |二十一点四十七分|八十三点九七| 62 |
| 2019 - 05 - 25 00：00：00| SLA11502| TXDP-12-SLA11502| 1 |二十点四十七|八十二点九|空闲| 61 | 3 | 2 |null| null| null| null| null| null|
| 2019 -12- 23 19：00：00| SLA11502| TXDP-12-SLA11502| 1 |二十点四十七|八十二点九|空闲| 5 | 2 | 3 |null| null| null| null| null| null|
| 2019 -12- 23 19：53| SLA11502| TXDP-12-SLA11502| 1 |二十点四十七|八十二点九|空闲| 27 | 1 | 4 |null| null| null| null| null| null|
我尝试了这个查询，但没有像预期的那样工作：

SELECT
    mid.created_date,
    mid.device,
    mid.rake_device,
    mid.is_same_location,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.rounded_geo_lat END) AS max_idle_lat1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.rounded_geo_lng END) AS max_idle_lng1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.time_diff_idle_vs_moving END) AS time_diff1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.rounded_geo_lat END) AS max_idle_lat2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.rounded_geo_lng END) AS max_idle_lng2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.time_diff_idle_vs_moving END) AS time_diff2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.rounded_geo_lat END) AS max_idle_lat3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.rounded_geo_lng END) AS max_idle_lng3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.time_diff_idle_vs_moving END) AS time_diff3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.rounded_geo_lat END) AS max_idle_lat4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.rounded_geo_lng END) AS max_idle_lng4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.time_diff_idle_vs_moving END) AS time_diff4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.rounded_geo_lat END) AS max_idle_lat5,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.rounded_geo_lng END) AS max_idle_lng5,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.time_diff_idle_vs_moving END) AS time_diff5
FROM
    MaxIdleData mid
WHERE
    mid.rn_idle = 1
GROUP BY
    mid.created_date, mid.device, mid.rake_device, mid.is_same_location;

型