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