我试图获得2个最新的记录来绘制结果。
我需要获取最新和第二个最新（以前）记录的“公司名称”、“第一个日期”和“本地存储百分比”。问题是最新日期显示的是第二个最新（以前）记录的百分比，该记录也属于第二个最新日期。

用户表

| id | company_name | usr_active | datto_host |
+----+--------------+------------+------------+
| 1  | Company A    |    1       | companyA   |
| 2  | Company B    |    1       | companyB   |
| 3  | Company C    |    1       | companyC   |

DATTO表

| id | device_model | date_recorded | local_storage_percent |   host   |
+----+--------------+---------------+-----------------------+----------+
| 106| ModelA       |   2023-03-23  |     95%               | companyA | 
| 108| ModelA       |   2023-03-22  |     83%               | companyA |
| 111| ModelC       |   2023-03-23  |     90%               | companyC |

查询：

select datto.device_model,  users.company_name, max(date_recorded) as first_date, max(datto.local_storage_percent) as local_storage_percent_current,
   substring_index(substring_index(group_concat(date_recorded order by date_recorded desc), ',', 2), ',', -1) as second_date,
   substring_index(substring_index(group_concat(local_storage_percent order by local_storage_percent desc), ',', 2), ',', -1) as local_storage_percent_previous from datto inner join users on datto.host = users.datto_host  where users.usractive = '1' group by users.company_name;

结果

| device_model | company_name | first_date | local_storage_percent_current | second_date | local_storage_percent_previous |
+--------------+--------------+------------+-------------------------------+-------------+--------------------------------+
| ModelA       |   Company A  | 2023-03-23 |             83%               | 2023-03-22  |           83%                  |

我期待的是：

| device_model | company_name | first_date | local_storage_percent_current | second_date | local_storage_percent_previous |
+--------------+--------------+------------+-------------------------------+-------------+--------------------------------+
| ModelA       |   Company A  | 2023-03-23 |             95%               | 2023-03-22  |           83%                  |

最新日期的local_storage_percent_current实际上与第二个日期相同。