mysql排序分组新问题（求救各位sql高手)

Question

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
**/之前FlySQL这种做法是id一个挨着一个排序就可以实现这种效果，现在顺序不挨着就排不出来了比如：
INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tests VALUES ('5', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tests VALUES ('6', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tests VALUES ('7', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tests VALUES ('13', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tests VALUES ('14', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tests VALUES ('17', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tests VALUES ('20', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tests VALUES ('30', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tests VALUES ('32', '1', '2011-06-01 21:31:45', '3');这种数据用上面那种方法就排列不出来,请教各位高手（先谢过了）注意是mysql版本的语法
最好是把数据调成
/**
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   2011-06-01 00:02:48
5   7   2011-06-01 00:02:48   2011-06-01 21:31:45   
6   3   2011-06-01 21:31:45   NULL
**/ 
 
 

Answer 1

用INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tests VALUES ('5', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tests VALUES ('6', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tests VALUES ('7', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tests VALUES ('13', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tests VALUES ('14', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tests VALUES ('17', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tests VALUES ('20', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tests VALUES ('30', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tests VALUES ('32', '1', '2011-06-01 21:31:45', '3');数据，要求结果是什么

Answer 2

-----------------------------------------这是要求的结果
/**
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   2011-06-01 00:02:48
5  7   2011-06-01 00:02:48   2011-06-01 21:31:45   
6  3   2011-06-01 21:31:45   NULL
**/  
--------------------------------------已经有实现方法
set @rowNo = 0;
set @number= 0;
set @num= 0;
select  
  (@rowNo := @rowNo + 1) as id,
  sum(counts) as `总记录数`,
  min(dates) as `开始时间`,
  case when max(dates)=min(dates) then min(dates) else max(dates) end as `结束时间`
from(select *,
t.d-(select count(1) from (select
(@number :=@number+1) as d,
id,
acst,
dates,
counts
from tests) as swhere s.acst=t.acst and s.d<t.d) as groupid
from(select
(@num :=@num+1) as d,
id,
acst,
dates,
counts
from tests)
as t
) a
group by groupid,acst--------------注：不过这个方法的实现效率比较低，在几十w数据的库中都很难返回查询结果（补充一下：查询后面还需带条件，所以就非常慢。）希望高手指点一下。。

Answer 3

其实理想效果要的是5 7 2011-06-01 00:02:48 2011-06-01 21:31:45这种如果这种实现很难就暂时用5 7 2011-06-01 00:02:48 2011-06-01 00:02:48这种吧稍后可以用程序再处理下就可以了。主要现在是这个sql加条件查起来非常慢。很纠结

Answer 4

4 3 2011-06-01 21:31:45 2011-06-01 00:02:48：怎么得出的
SET @num=0;
SET @a='';
SELECT pm,MAX(dates),MIN(dates),SUM(counts) FROM (
SELECT *,@num:=IF(@a=acst,@num,@num+1)AS pm ,@a:=acst FROM tests) a GROUP BY pm;

Answer 5

这个方法果然好使，非常感谢wwwwb大哥在千w级的数据中查询还是很快！！结贴散分了也谢谢大家参与支持