表结构如下| 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相同的分组求最大最小时间| 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
| 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相同的分组求最大最小时间| 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
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
你这个结果不对mysql> USE AA;
Database changed
mysql> SET @a='';
Query OK, 0 rows affected (0.00 sec)mysql> SET @b=1;
Query OK, 0 rows affected (0.00 sec)mysql> 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;
+------------+------+---------------------+---------------------+
| id | bz | MIN(cdate) | MAX(cdate) |
+------------+------+---------------------+---------------------+
| 0800032955 | 6 | 2011-01-19 00:00:00 | 2011-01-21 00:00:00 |
| 0800032956 | 7 | 2011-01-22 00:00:00 | 2011-01-22 00:00:00 |
| 0800250910 | 3 | 2011-01-13 00:00:00 | 2011-01-14 00:00:00 |
| 0800250910 | 5 | 2011-01-17 00:00:00 | 2011-01-18 00:00:00 |
| 0800309603 | 2 | 2011-01-11 00:00:00 | 2011-01-12 00:00:00 |
| 0800309603 | 4 | 2011-01-15 00:00:00 | 2011-01-16 00:00:00 |
+------------+------+---------------------+---------------------+
6 rows in set (0.02 sec)mysql>
Query OK, 0 rows affected (0.00 sec)mysql> SET @b=0;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT c.*,d.cdate FROM (
-> SELECT *,@a:=@a+1 AS bz
-> FROM ttl3 a WHERE NOT EXISTS(SELECT 1 FROM ttl3 WHERE a.`ID`=id AND a.`C
DATE`+INTERVAL 1 DAY =`CDATE`)) c
-> LEFT JOIN
-> (SELECT *,@b:=@b+1 AS bz FROM ttl3 a WHERE NOT EXISTS(SELECT 1 FROM ttl3
WHERE a.`ID`=id
-> AND a.`CDATE`-INTERVAL 1 DAY=cdate)) d
-> ON c.bz=d.bz;
+------------+---------------------+-----+------+---------------------+
| ID | CDATE | id1 | bz | cdate |
+------------+---------------------+-----+------+---------------------+
| 0800309603 | 2011-01-12 00:00:00 | 2 | 1 | 2011-01-11 00:00:00 |
| 0800250910 | 2011-01-14 00:00:00 | 4 | 2 | 2011-01-13 00:00:00 |
| 0800309603 | 2011-01-16 00:00:00 | 6 | 3 | 2011-01-15 00:00:00 |
| 0800250910 | 2011-01-18 00:00:00 | 8 | 4 | 2011-01-17 00:00:00 |
| 0800032955 | 2011-01-21 00:00:00 | 11 | 5 | 2011-01-19 00:00:00 |
| 0800032956 | 2011-01-22 00:00:00 | 12 | 6 | 2011-01-22 00:00:00 |
+------------+---------------------+-----+------+---------------------+
6 rows in set (0.00 sec)mysql>
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
ORDER BY 2,1
加入排序即可
第2个自行更改字段名即可