select Id=(select COUNT(1) from ( select groupid=ID-(select COUNT(1) from #temp v where v.asct<w.asct ) from #temp w ) x where x.groupid<a.groupid) ,总记录数=count(1) ,开始时间=MIN(dates) ,结束时间=case when (MAX(dates)=MIN(dates)) then null else MAX(dates) end from ( select groupid=ID-(select COUNT(1) from #temp v where v.asct<w.asct ) from #temp w ) a group by asct,GROUPid order by MAX(id)-- 试试,两千就该是这种
CREATE TABLE `tests` ( `id` int(10) NOT NULL DEFAULT '0', `acst` int(10) DEFAULT NULL, `dates` datetime DEFAULT NULL, `counts` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2'); INSERT INTO tests VALUES ('2', '1', '2011-06-01 00:01:18', '2'); INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:34', '5'); INSERT INTO tests VALUES ('4', '0', '2011-06-01 00:01:37', '6'); INSERT INTO tests VALUES ('5', '0', '2011-06-01 00:01:38', '3'); INSERT INTO tests VALUES ('6', '1', '2011-06-01 00:01:46', '10'); INSERT INTO tests VALUES ('7', '1', '2011-06-01 00:02:00', '6'); INSERT INTO tests VALUES ('8', '1', '2011-06-01 00:02:05', '3'); INSERT INTO tests VALUES ('9', '1', '2011-06-01 00:02:21', '5'); INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:02:28', '21'); INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:43', '7'); INSERT INTO tests VALUES ('12', '0', '2011-06-01 00:02:48', '7'); INSERT INTO tests VALUES ('13', '1', '2011-06-01 21:31:45', '3');set @rowNo = 0; select (@rowNo := @rowNo + 1) as id, sum(counts) as `总记录数`, min(dates) as `开始时间`, case when max(dates)=min(dates) then NULL else max(dates) end as `结束时间` from( select *, id-(select count(1) from tests where acst=t.acst and id<t.id) as groupid from tests as t ) a group by groupid,acst/** 1 9 2011-06-01 00:01:04 2011-06-01 00:01:34 2 52 2011-06-01 00:01:46 2011-06-01 00:02:43 3 9 2011-06-01 00:01:37 2011-06-01 00:01:38 4 3 2011-06-01 21:31:45 NULL 5 7 2011-06-01 00:02:48 NULL **/
CREATE TABLE tb ( id INT , acst INT , dates DATETIME , counts INT )INSERT INTO tb VALUES ('1', '1', '2011-06-01 00:01:04', '2'); INSERT INTO tb VALUES ('2', '1', '2011-06-01 00:01:18', '2'); INSERT INTO tb VALUES ('3', '1', '2011-06-01 00:01:34', '5'); INSERT INTO tb VALUES ('4', '0', '2011-06-01 00:01:37', '6'); INSERT INTO tb VALUES ('5', '0', '2011-06-01 00:01:38', '3'); INSERT INTO tb VALUES ('6', '1', '2011-06-01 00:01:46', '10'); INSERT INTO tb VALUES ('7', '1', '2011-06-01 00:02:00', '6'); INSERT INTO tb VALUES ('8', '1', '2011-06-01 00:02:05', '3'); INSERT INTO tb VALUES ('9', '1', '2011-06-01 00:02:21', '5'); INSERT INTO tb VALUES ('10', '1', '2011-06-01 00:02:28', '21'); INSERT INTO tb VALUES ('11', '1', '2011-06-01 00:02:43', '7'); INSERT INTO tb VALUES ('12', '0', '2011-06-01 00:02:48', '7'); INSERT INTO tb VALUES ('13', '1', '2011-06-01 21:31:45', '3');SELECT (SELECT COUNT(1)+1 FROM ( SELECT id-(SELECT COUNT(1) FROM tb b WHERE acst=c.acst AND b.id<c.id )AS groupid FROM tb c GROUP BY groupid ) tt WHERE tt.groupid<t.groupid ) AS g, COUNT(*) AS 总记录数, MIN(dates) AS 开始时间, CASE WHEN (MAX(dates) = MIN(dates)) THEN NULL ELSE MAX(dates) END AS 结束时间 FROM ( SELECT id-(SELECT COUNT(1) FROM tb WHERE acst=a.acst AND id<a.id) AS groupid,id,acst,dates,counts FROM tb a ) t GROUP BY acst, groupid ORDER BY MAX(id)/* "groupid" "总记录数" "开始时间" "结束时间" "1" "3" "2011-06-01 00:01:04" "2011-06-01 00:01:34" "3" "2" "2011-06-01 00:01:37" "2011-06-01 00:01:38" "2" "6" "2011-06-01 00:01:46" "2011-06-01 00:02:43" "4" "1" "2011-06-01 00:02:48" \N "3" "1" "2011-06-01 21:31:45" \N */结果差点,呵呵 你自己看着改改吧
select Id=(select COUNT(1) from
(
select groupid=ID-(select COUNT(1) from #temp v where v.asct<w.asct ) from #temp w
) x where x.groupid<a.groupid)
,总记录数=count(1)
,开始时间=MIN(dates)
,结束时间=case when (MAX(dates)=MIN(dates)) then null else MAX(dates) end
from (
select groupid=ID-(select COUNT(1) from #temp v where v.asct<w.asct ) from #temp w
) a
group by asct,GROUPid order by MAX(id)-- 试试,两千就该是这种
`id` int(10) NOT NULL DEFAULT '0',
`acst` int(10) DEFAULT NULL,
`dates` datetime DEFAULT NULL,
`counts` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tests VALUES ('2', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tests VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tests VALUES ('5', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tests VALUES ('6', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tests VALUES ('7', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tests VALUES ('8', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tests VALUES ('9', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tests VALUES ('12', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tests VALUES ('13', '1', '2011-06-01 21:31:45', '3');set @rowNo = 0;
select
(@rowNo := @rowNo + 1) as id,
sum(counts) as `总记录数`,
min(dates) as `开始时间`,
case when max(dates)=min(dates) then NULL else max(dates) end as `结束时间`
from(
select *,
id-(select count(1) from tests where acst=t.acst and id<t.id) as groupid
from tests as t
) a
group by groupid,acst/**
1 9 2011-06-01 00:01:04 2011-06-01 00:01:34
2 52 2011-06-01 00:01:46 2011-06-01 00:02:43
3 9 2011-06-01 00:01:37 2011-06-01 00:01:38
4 3 2011-06-01 21:31:45 NULL
5 7 2011-06-01 00:02:48 NULL
**/
id INT ,
acst INT ,
dates DATETIME ,
counts INT
)INSERT INTO tb VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tb VALUES ('2', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tb VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tb VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tb VALUES ('5', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tb VALUES ('6', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tb VALUES ('7', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tb VALUES ('8', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tb VALUES ('9', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tb VALUES ('10', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tb VALUES ('11', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tb VALUES ('12', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tb VALUES ('13', '1', '2011-06-01 21:31:45', '3');SELECT
(SELECT COUNT(1)+1 FROM
(
SELECT id-(SELECT COUNT(1) FROM tb b WHERE acst=c.acst AND b.id<c.id )AS groupid FROM tb c GROUP BY groupid
) tt WHERE tt.groupid<t.groupid
)
AS g,
COUNT(*) AS 总记录数,
MIN(dates) AS 开始时间,
CASE WHEN (MAX(dates) = MIN(dates)) THEN NULL ELSE MAX(dates) END AS 结束时间
FROM
(
SELECT id-(SELECT COUNT(1) FROM tb WHERE acst=a.acst AND id<a.id) AS groupid,id,acst,dates,counts FROM tb a
) t
GROUP BY acst, groupid
ORDER BY MAX(id)/*
"groupid" "总记录数" "开始时间" "结束时间"
"1" "3" "2011-06-01 00:01:04" "2011-06-01 00:01:34"
"3" "2" "2011-06-01 00:01:37" "2011-06-01 00:01:38"
"2" "6" "2011-06-01 00:01:46" "2011-06-01 00:02:43"
"4" "1" "2011-06-01 00:02:48" \N
"3" "1" "2011-06-01 21:31:45" \N
*/结果差点,呵呵 你自己看着改改吧