MySQL:按列分组并获取日期范围

I have a table like this

我有一张这样的桌子

id - price - date
1  - 10    - 01/01/2017
2  - 10    - 02/01/2017
3  - 10    - 03/01/2017
4  - 10    - 04/01/2017
5  - 10    - 05/01/2017
6  - 20    - 06/01/2017
7  - 20    - 07/01/2017
8  - 20    - 08/01/2017
9  - 20    - 09/01/2017
10 - 10    - 10/01/2017
11 - 10    - 11/01/2017
12 - 10    - 12/01/2017
13 - 10    - 13/01/2017

And I want to show result this way

我想用这种方式展示结果

10 - 01/01/2017 - 05/01/2017
20 - 06/01/2017 - 09/01/2017
10 - 10/01/2017 - 13/01/2017

My query is

我的查询

SELECT price, min(date), max(date) FROM mytable GROUP BY price

but result is

但结果是

20 - 06/01/2017 - 09/01/2017
10 - 10/01/2017 - 13/01/2017

Can I get right result with mysql query or must find a php solution?

我可以用mysql查询得到正确的结果吗?还是必须找到php解决方案?

2 个解决方案

#1

This is a gaps and islands problem. You can use variables in order to detect islands of consecutive records having the same price value:

这是一个差距和岛屿问题。您可以使用变量来检测具有相同价格值的连续记录的岛屿:

SELECT price, MIN(`date`) AS start_date, MAX(`date`) AS end_date
FROM (
   SELECT id, price, `date`,
          @rn := @rn + 1 AS rn,
          -- If price remains the same then increase island population,
          -- otherwise reset it
          @seq := IF(@p = price, @seq + 1,
                      IF(@p := price, 1, 1)) AS seq
   FROM mytable
   CROSS JOIN (SELECT @p := 0, @rn := 0, @seq := 0) AS vars
   ORDER BY `date`) AS t
GROUP BY price, rn - seq

#2

SELECT price, min(date), max(date) FROM mytable GROUP BY price order by price