Mysql组数据按时间 [英] Mysql Group Data By Time

问题描述

更新的问题:

这是数据

   m_to    m_from  m_id    m_time 
        5   5   1   1374769716
        5   5   2   1374771178
        5   5   3   1374771294
        5   5   4   1374771396
        5   5   5   1374771784
        1   5   6   1374772120
        1   5   7   1374773097
        5   1   8   1374773579
        5   1   9   1374774095
        5   1   10  1374774148
        1   5   11  1374777304
        444 5   12  1374779752
        5   444 13  1374780378
        5   5   14  1374781374
        5   5   15  1374832375
        444 5   16  1374837258
        5   444 17  1374837525
        5   444 18  1374838801
        444 5   19  1374838976
        1   5   20  1374842736
        5   5   21  1374842954
        444 5   22  1374843389
        5   5   23  1374843466
        1   5   24  1374843853
        1   5   25  1374848855
        444 5   26  1374848889
        5   5   27  1374848912
        1   5   28  1374849001
        5   5   29  1374849056
        444 5   30  1374850406

第一个column是"m_to"，第二个是m_from，第三个是m_id，fourht是m_time

First coulmn is "m_to" second is m_from and third is m_id and fourht is m_time

现在我要的是，我想按m_from分组，按m_time排序. ut将要添加的新消息放在最上面，下一行应该放在第二个……等等

Now what I want is, I wanna group by m_from and order by m_time. ut new message which ever wwill be added should be on top and the next row should be on second......etc

我用过.

SELECT m_from,m_time FROM messages WHERE m_to='5' GROUP BY m_from ORDER BY m_time DESC

但正在以这样的方式进行回复

but is resuting in something like this

444
1
5

因此，它无法正确排序数据.

so, its not correctly sorting data..

推荐答案

您可以通过执行以下操作来获取与最后一条消息相对应的m_from，该消息出现在列表的顶部:

You can get the m_from that correspond to the last message to appear at the top of the list by doing something like this :

SELECT m_from, MAX(m_time) AS m_time
FROM messages 
GROUP BY m_from 
ORDER BY m_time DESC

但是很难说，不知道您想要什么输出...

but hard to say without knowing what is the output that you want...

希望这个可以工作:

SELECT m_user, MAX(m_id) as m_id, MAX(m_time) AS m_time
FROM (
SELECT m_from AS m_user, MAX(m_id) as m_id, MAX(m_time) AS m_time
 FROM messages 
 WHERE m_to = 5
 GROUP BY m_from
UNION
SELECT m_to AS m_user, MAX(m_id) as m_id, MAX(m_time) AS m_time
 FROM messages 
 WHERE m_from = 5
 GROUP BY m_to
 ) AS result
GROUP BY m_user
ORDER BY m_time DESC

这篇关于Mysql组数据按时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！