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分组的效果。
`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分组的效果。
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;
没有生成怎么运行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;
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个变量