sql疑难纠结问题，请各位大虾帮帮忙。。。

Question

CREATE TABLE `temp` (
  `id` int(11) NOT NULL DEFAULT '0',
  `egst` int(11) DEFAULT NULL,
  `dates` datetime DEFAULT NULL,
  `counts` float(11,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of temp
-- ----------------------------
INSERT INTO temp VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO temp VALUES ('2', '1', '2011-06-01 00:01:18', '3');
INSERT INTO temp VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO temp VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO temp VALUES ('6', '0', '2011-06-01 00:01:38', '8');
INSERT INTO temp VALUES ('9', '1', '2011-06-01 00:01:46', '2');
INSERT INTO temp VALUES ('10', '1', '2011-06-01 00:02:00', '3');
INSERT INTO temp VALUES ('12', '1', '2011-06-01 00:02:05', '0');
INSERT INTO temp VALUES ('17', '1', '2011-06-01 00:02:21', '0');
INSERT INTO temp VALUES ('21', '1', '2011-06-01 00:02:28', '8');
INSERT INTO temp VALUES ('22', '1', '2011-06-01 00:02:43', '15');
INSERT INTO temp VALUES ('23', '1', '2011-06-01 00:02:48', '21');
INSERT INTO temp VALUES ('25', '0', '2011-06-01 00:31:45', '3');
INSERT INTO temp VALUES ('28', '1', '2011-06-01 00:34:13', '6');
------------------想要得到结果
id          mindate                     maxdate              counts
1 2011-06-01 00:01:04 2011-06-01 00:01:34      3（说明：5-3）
2 2011-06-01 00:01:37 2011-06-01 00:01:38      2（说明：8-6）
3 2011-06-01 00:01:46 2011-06-01 00:02:48      14（说明：（3-2）+（21-8））
4 2011-06-01 00:31:45 2011-06-01 00:31:45      0（说明：只有一条记录取0）
5 2011-06-01 00:34:13 2011-06-01 00:34:13      0（说明：只有一条记录取0）说明一下，首先是根据字段egst的（0，1）来分组，分成5个组，然后还需要根据counts字段是否为0来分组，然后根据分组后counts值的尾减去首，再把得到的结果集相加。-------------------------------------------现有sql，能实现egst的分组效果。
SET @num=0;
SET @a='';
SELECT pm,MIN(dates),MAX(dates) FROM (
SELECT *,@num:=IF(@a=egst,@num,@num+1)AS pm ,@a:=egst FROM temp) a GROUP BY pm ORDER BY dates;求求各位大虾帮忙实现counts分组的效果。

Answer 1

建议用程序或者存储过程来实现吧，用SQL语句的效率肯定不好。

Answer 2

恩程序也在同步设计中能够找到sql实现是最简单也是最高效了。。请各位sql大虾发发力

Answer 3

SQL看上去虽然是一句，但实现这个算法的执行效率会很差。特别是记录很多的时候。

Answer 4

上面这个sql效率还挺高的，在2千w的记录中加条件查询结果大概2到3秒，不知道再根据counts分组效率会怎么样。。

Answer 5

SELECT MAX(A1),MIN(A2),SUM(SS) FROM (
SELECT A1.DATES AS A1,A2.DATES AS A2,A1.COUNTS-A2.COUNTS AS SS ,A1.PM,A1.PM1 FROM (
SELECT * FROM ALSB A
WHERE NOT EXISTS(SELECT 1 FROM ALSB WHERE A.pm=PM AND A.PM1=PM1 AND A.ID<ID AND COUNTS<>0 ) AND COUNTS<>0) A1
LEFT JOIN (SELECT * FROM ALSB A
WHERE NOT EXISTS(SELECT 1 FROM ALSB WHERE A.pm=PM AND A.PM1=PM1 AND A.ID>ID AND COUNTS<>0) AND COUNTS<>0) A2
ON A1.PM=A2.PM AND A1.PM1=A2.PM1) A3 GROUP BY A3.PM;ALSB:
SET @a='';
SET @NUM=0;
SET @num1=0;
SET @b=1;
CREATE TABLE ALSB AS  SELECT *,@NUM1:=IF(@B=PM,IF(COUNTS<>0,@NUM1,@NUM+1),@NUM1+1) AS PM1,@B:=PM FROM ( SELECT *,@num:=IF(@a=egst,@num,@num+1) AS pm ,@a:=egst FROM temp) A;

Answer 6

请问WWWWA大哥执行上面那个nknown column 'A.pm' in 'where clause'报这个错误。

Answer 7

生成ALSB没有，我测试是通过的

Answer 8

没有生成，一起执行也是说1146 - Table 'alsb' doesn't exist说这个表不存在，一起执行和分开执行都不行。请问这个是mysql的语法么。。

Answer 9

没有生成怎么运行SET @a='';
SET @NUM=0;
SET @num1=0;
SET @b=1;
CREATE TABLE ALSB AS SELECT *,@NUM1:=IF(@B=PM,IF(COUNTS<>0,@NUM1,@NUM+1),@NUM1+1) AS PM1,@B:=PM FROM ( SELECT *,@num:=IF(@a=egst,@num,@num+1) AS pm ,@a:=egst FROM temp) A;
SELECT MAX(A1),MIN(A2),SUM(SS) FROM (
SELECT A1.DATES AS A1,A2.DATES AS A2,A1.COUNTS-A2.COUNTS AS SS ,A1.PM,A1.PM1 FROM (
SELECT * FROM ALSB A
WHERE NOT EXISTS(SELECT 1 FROM ALSB WHERE A.pm=PM AND A.PM1=PM1 AND A.ID<ID AND COUNTS<>0 ) AND COUNTS<>0) A1
LEFT JOIN (SELECT * FROM ALSB A
WHERE NOT EXISTS(SELECT 1 FROM ALSB WHERE A.pm=PM AND A.PM1=PM1 AND A.ID>ID AND COUNTS<>0) AND COUNTS<>0) A2
ON A1.PM=A2.PM AND A1.PM1=A2.PM1) A3 GROUP BY A3.PM;

Answer 10

谢谢WWWWA大哥，这种创建表的方式是可以实现效果，创建表的方式不是很好。。因为表的数据量太大了。

Answer 11

那你将
ALSB
修改为
SELECT *,@NUM1:=IF(@B=PM,IF(COUNTS<>0,@NUM1,@NUM+1),@NUM1+1) AS PM1,@B:=PM FROM ( SELECT *,@num:=IF(@a=egst,@num,@num+1) AS pm ,@a:=egst FROM temp) A;
这样的语句要用16个变量

Answer 12

恩我用程序实现了，还是非常感谢wwwwb大哥。。结贴散分了