mysql获取组数据的第一行和最后一行

Question

我有一个事件表，

| id | title  | date       |
| 1  | party  | 2011-02-02 |
| 2  | party  | 2011-02-03 |
| 3  | party  | 2011-02-04 |
| 4  | party  | 2011-02-05 |
| 5  | brunch | 2011-05-20 |
| 6  | brunch | 2011-05-21 |
| 7  | brunch | 2011-05-22 |
| 8  | brunch | 2011-05-23 |

我想把数据传输到这个结构

| id | title  | start      | finish     |
| 1  | party  | 2011-02-02 | 2011-02-05 |
| 2  | brunch | 2011-05-20 | 2011-05-23 |

什么是最好的方法？

Answer 1

没有新ID:

SELECT title, MIN(date) AS start, MAX(date) AS finish 
FROM event
GROUP BY title

要生成新 ID，请使用计数器:

SET @counter = 0; 
SELECT ((@counter := (@counter+1))) AS id, title, MIN(date) AS start, MAX(date) AS finish     
FROM event
GROUP BY title