mysql 先排序后分组问题

Question

表结构如下| ID                   | CDATE         |
| 0800309603  | 2011-01-11  |
| 0800309603  | 2011-01-12  | 
| 0800250910  | 2011-01-13  |
| 0800250910  | 2011-01-14  |
| 0800309603  | 2011-01-15  |
| 0800309603  | 2011-01-16  |
| 0800250910  | 2011-01-17  |
| 0800250910  | 2011-01-18  |
| 0800032955  | 2011-01-19  |
| 0800032955  | 2011-01-20  |
| 0800032955  | 2011-01-21  |
| 0800032956  | 2011-01-22  |需要结果如下| ID                   | MIN_CDATE | MAX_CDATE |  
| 0800309603  | 2011-01-11  | 2011-01-12  |
| 0800250910  | 2011-01-13  | 2011-01-14  |
| 0800309603  | 2011-01-15  | 2011-01-16  |
| 0800250910  | 2011-01-17  | 2011-01-18  |
| 0800032955  | 2011-01-19  | 2011-01-21  |
| 0800032956  | 2011-01-22  | 2011-01-22  |求SQL

Answer 1

table1
| ID                   | CDATE         |
| 0800309603  | 2011-01-11  |
| 0800309603  | 2011-01-12  | 
| 0800250910  | 2011-01-13  |
| 0800250910  | 2011-01-14  |
| 0800309603  | 2011-01-15  |
| 0800309603  | 2011-01-16  |
| 0800250910  | 2011-01-17  |
| 0800250910  | 2011-01-18  |
| 0800032955  | 2011-01-19  |
| 0800032955  | 2011-01-20  |
| 0800032955  | 2011-01-21  |
| 0800032956  | 2011-01-22  |
select * from (
select ID,MAX(CDATE) as MAX_CDATE from table1
where ID in (select distinct ID from table1) ) t1
left join (
select ID,MIN(CDATE) as MIN_CDATE from table1
where ID in (select distinct ID from table1) ) t2 on t1.ID = t2.ID
代码没验证过，不晓得对不对，你自己试一下

Answer 2

select 
id,
min(CDATE) as MIN_CDATE ,
max(CDATE) as MAX_CDATE
from tablename 
group by id;

Answer 3

mysql> select * from dd;
+-----------+------------+
| id        | DATE       |
+-----------+------------+
| 800309603 | 2013-06-03 |
| 800309603 | 2013-06-04 |
| 800309603 | 2013-06-12 |
| 800309603 | 2013-05-30 |
| 800309622 | 2013-06-05 |
| 800309622 | 2013-06-04 |
| 800309627 | 2013-06-20 |
| 800309627 | 2013-06-12 |
| 800309627 | 2013-06-14 |
+-----------+------------+
9 rows in setmysql> select 
id,
min(DATE) as earliestTime ,
max(DATE) as latestTime
from dd 
group by id;
+-----------+--------------+------------+
| id        | earliestTime | latestTime |
+-----------+--------------+------------+
| 800309603 | 2013-05-30   | 2013-06-12 |
| 800309622 | 2013-06-04   | 2013-06-05 |
| 800309627 | 2013-06-12   | 2013-06-20 |
+-----------+--------------+------------+
3 rows in set

Answer 4

SET @a='';
SET @b=1;
SELECT id,bz,MIN(cdate),MAX(cdate) FROM (
SELECT *,@b:=IF(@a=id,@b,@b+1) AS bz,@a:=id FROM ttl3) a
GROUP BY id,bz

Answer 5

以上查询 按id升序排列，
如需降序，尾端加desc即可