计算8月1日机场的单位时间内航班起飞次数,其中时间单位为10分钟。即计算每10分钟内机场有多少架航班起飞,并以时间为x轴,次数为y轴,画一张图表表示上述数据,如何来实现啊begin_date
2013-08-01 00:08:00
2013-08-01 15:08:00
2013-08-01 10:35:00
2013-08-01 18:39:00
2013-08-01 20:55:00
.
.
.x轴起点是 2013-08-01 00:00:00 ,终点是 2013-08-02 00:00:00
sql
2013-08-01 00:08:00
2013-08-01 15:08:00
2013-08-01 10:35:00
2013-08-01 18:39:00
2013-08-01 20:55:00
.
.
.x轴起点是 2013-08-01 00:00:00 ,终点是 2013-08-02 00:00:00
sql
select substring(begin_date,1,13),count(*)
from tb
group by substring(begin_date,1,13)至于怎么画图需要你程序调用别的东西来做
楼主的应该是按 小时分组了
应该是
select substring(begin_date,1,15),count(*)
from tb
group by substring(begin_date,1,15)
把十三改成15,
或者
select left(begin_date,15),count(*)
from tb
group by left(begin_date,15)
其实都差不多
where begin_date between ‘2013-08-01’and ‘2013-08-02’
lsb,字段F1、F2,保存开始、结束时间
:
DELIMITER $$USE `aa`$$DROP PROCEDURE IF EXISTS `tt2`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `tt2`(A1 DATETIME,A2 DATETIME)
BEGIN
DECLARE II INT DEFAULT 1;
SET @a1=A1;
WHILE @A1<=A2 DO
INSERT INTO lsb VALUES(@A1,DATE_ADD(@a1, INTERVAL IF(II=1,10,9) MINUTE));
SET @a1=DATE_ADD(@a1, INTERVAL IF(II=1,11,10) MINUTE);
SET II=2;
END WHILE;
END$$DELIMITER ;调用:
CALL TT2('2013-08-01 00:00:00','2013-08-02 00:00:00')计算次数:
select b.f1,b.f2,count(a.*) from tt a inner join lsb b on a.时间字段 between b.f1 and b.f2
group by b.f1,b.f2以此查询为数据源,用EXCEL之类的软件作图即可
from tttt
group by begin_date-interval minute(begin_date) % 10 minute - interval second(begin_date)