MySQL 特定分组依据 [英] MySQL Specific Group by
本文介绍了MySQL 特定分组依据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我们有下表:
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屋!
查看全文