MySQL组通过某种类型并选择最新的行？ [英] MySQL group by a certain type and select the latest row?

问题描述

设想一下列表类型，日期，消息。有些行看起来像这样（type | date | message）：

  1 | 1310572318 |你好
 1 | 1310572317 | Hi 
 2 | 1310572315 | Wassup 
 3 | 1310572312 | Yo 
 3 | 1310572311 |嘿
 3 | 1310572309 | Eyo 
 1 | 1310572305 |你好
 1 | 1310572303 |好日子
  

是否可以按类型对它们进行分组，然后选择最新（按日期排序）结果是：

  1 | 1310572318 |您好
 2 | 1310572315 | Wassup 
 3 | 1310572312 | Yo 
 1 | 1310572305 |您好
  

我很确定我必须使用一些MySQL集合函数，但我不是很善于处理它们，所以我在这里寻求一点帮助。

没有任何聚合可以使用不同的列，以便您可以获得

这可能会有帮助：

  SELECT b $ b`type`，
 MAX（`date`）AS`max_date`，
（SELECT`t2`.`message` FROM`table` AS`t2` WHERE`t2` .`type` =`t1`.`type` ORDER BY`t2`.`date` DESC LIMIT 1）
 FROM`table` AS`t1` 
 GROUP BY`type` 
  

Imagine a table with columns type, date, message. And some rows looking like this (type | date | message):

1 | 1310572318 | Hello
1 | 1310572317 | Hi
2 | 1310572315 | Wassup
3 | 1310572312 | Yo
3 | 1310572311 | Hey
3 | 1310572309 | Eyo
1 | 1310572305 | Hello
1 | 1310572303 | Good Day

Is it possible to group them by type, and selecting the latest (ordered by date) so the result would be:

1 | 1310572318 | Hello
2 | 1310572315 | Wassup
3 | 1310572312 | Yo
1 | 1310572305 | Hello

I'm pretty sure I have to use some MySQL Aggregate functions, but I'm not very good at them, so I'm asking for a little bit of help here.

解决方案

There isn't any aggregate that can use a different column so that you could get the message with the latest date.

This could be helpful:

SELECT 
    `type`, 
    MAX(`date`) AS `max_date`, 
    (SELECT `t2`.`message` FROM `table` AS `t2` WHERE `t2`.`type` = `t1`.`type` ORDER BY `t2`.`date` DESC LIMIT 1)
FROM `table` AS `t1`
GROUP BY `type`

这篇关于MySQL组通过某种类型并选择最新的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！