56,687
社区成员
发帖
与我相关
我的任务
分享
SET @dt := (SELECT MIN(`date`) FROM albedotest);
SELECT @str := CONCAT('SELECT MIN(`date`),
FLOOR(DATEDIFF(`date`,''', @dt, ''')/5) AS dt,
AVG(albvalue) AS avgvalue FROM albedotest GROUP BY dt');
PREPARE rc FROM @str;
EXECUTE rc;
CREATE TABLE albedotest
(
id int(10) NOT NULL PRIMARY KEY auto_increment,
date DATE,
time TIME,
albvalue double(12,6)
);
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-27','17:50:00','0.333188');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-27','18:00:00','0.339575');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-28','14:00:00','0.335765');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-28','14:10:00','0.567785');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-29','00:10:00','0.348575');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-29','00:20:00','0.468775');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-30','09:30:00','0.348767');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-04-30','09:40:00','0.571264');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-05-01','16:00:00','0.339576');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-05-01','16:10:00','0.471543');
INSERT INTO albedotest (date,time,albvalue) VALUES('2014-05-02','13:10:00','0.112356');
……
想要得到如下的结果:
date avgvalue
2014-04-27 value1
2014-05-02 value2
2014-05-07 value3
原表中每天不止两条数据,这里只是举个例子,数据库用的是MySQL 5.5
SELECT CONVERT((DATEDIFF(albdate, '1900-01-01')/5), DECIMAL(10,0)) AS a, albAlbedo FROM tbl01 GROUP BY a