MySQL 特定分组依据 [英] MySQL Specific Group by

问题描述

假设我们有下表:

id  |  name  |  time
 1  |  Test  |  10
 2  |  Test  |  24
 3  |  Test  |  30
 4  |  ABCD  |  10
 5  |  ABCD  |  89
 6  |  EFGH  |  5
 7  |  WXYZ  |  5
 8  |  WXYZ  |  7
 9  |  Test  |  10
10  |  Test  |  24
11  |  EFGH  |  7

我想通过查询获得以下输出:

I want to get the following output with a query:

name  |  min_time  |  max_time
Test  |        10  |        30
ABCD  |        10  |        89
EFGH  |         5  |         5
WXYZ  |         5  |         7
Test  |        10  |        24
EFGH  |         7  |         7

类似于 GROUP BY 但有历史记录...

Something like GROUP BY but with a history...

SELECT name, MIN(time), MAX(time)
FROM table
GROUP BY name
ODER BY id ASC

使用此查询，它只会将每个名称组合在一起...但我希望它仅在行之后或之前具有相同名称时才组合在一起.

With this query it would only group EVERY name together... But I want it only grouped when the same name is after or before the row.

有人知道具体怎么做吗?

Does someone know how to do this exactly?

推荐答案

应该这样做:

select name, min(time), max(time)
from (
select
name, 
time,
@group_num := if(@name != name, @group_num + 1, @group_num) AS group_number,
@name := name
from yourTable
, (select @group_num := 1, @name := NULL) variables
order by id
) sq
group by group_number

这篇关于MySQL 特定分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！