获取行由最新时间的MySQL过滤 [英] Getting rows filtered by most recent time mysql

问题描述

我有一个复杂的查询问题，我不知道该怎么写. 我有一张桌子:

I have a question of a complex query and I don't know how to write it. I have the next table:

+---------------+-----------+----------+--------------------------+
|id_incidencia  | id_alarma | idaction | concat(fecha, ' ', hora) |
+---------------+-----------+----------+--------------------------+
| 18488         |      551  |        1 | 2016-08-29 14:55:47      |
| 18491         |      551  |        7 | 2016-08-29 15:02:47      |
| 18493         |      551  |        6 | 2016-08-29 15:37:39      |
| 18495         |      551  |        7 | 2016-08-29 15:41:27      |
| 19263         |      551  |        6 | 2016-09-16 15:17:27      |
| 19429         |      5151 |        5 | 2016-09-19 09:01:17      |
| 19447         |      5151 |        5 | 2016-09-19 09:25:23      |
| 19500         |      5151 |        5 | 2016-09-20 09:38:11      |
| 19532         |      5151 |        5 | 2016-09-23 15:02:50      |
| 19526         |      5151 |        6 | 2016-09-23 11:23:27      |
| 19528         |      5151 |        6 | 2016-09-23 11:28:01      |
| 19527         |      5151 |        7 | 2016-09-23 11:25:18      |
| 19529         |      5151 |        7 | 2016-09-23 11:28:08      |
+---------------+-----------+----------+--------------------------+

我想从查询中得到下一个结果:

I'd like to have the next result from a query:

idaction | id_alarma | datetime
---------+-----------+--------- 
7        |    551    | 2016-08-29 15:41:27 
7        |    5151   | 2016-09-23 11:28:08

这意味着我想为每组id_alarma值，我要获得最近时间的最后一行，其值为idaction.问题在于，当我执行下一个查询时，idaction中的值与datetime字段匹配的值不同.

That's means that I want for each group of id_alarma value, I want to get the last row with the most recently time and it's value of idaction. The problem is that when I perform the next query, the value in idaction is not the same that match with the datetime field.

我提到的查询是:

SELECT     a.id, 
           nia.idaction, 
           MAX(CONCAT(nia.fecha, ' ', nia.hora)) 
FROM       nectar_incidencias_alarma nia 
INNER JOIN alarmas a 
        ON a.id=nia.id_alarma 
WHERE      nia.idaction IN (6,7) 
GROUP BY   a.id

推荐答案

要检索 idaction 的正确值，可以将group_concat与substring_index结合使用.请注意，您实际上不需要加入表 alarmas ，因为您在 nectar_incidencias_alarma 中拥有所需的全部内容:

To retrieve the correct value of idaction, you could use group_concat in combination with substring_index. Note that you don't really need to join the table alarmas, as you have all you need in nectar_incidencias_alarma:

SELECT     id_alarma,
           SUBSTRING_INDEX(GROUP_CONCAT(idaction 
                           ORDER BY CONCAT(fecha, ' ', hora) DESC), ',', 1)+0 id_action,
           MAX(CONCAT(fecha, ' ', hora)) 
FROM       nectar_incidencias_alarma
WHERE      idaction IN (6,7) 
GROUP BY   id_alarma

这篇关于获取行由最新时间的MySQL过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！