如何查询一天的数据分为24小时,目前的数据查出来select DATE_FORMAT(daytime,'%Y-%m-%d %H时'), Ptype,dinggou,tuiding
from table where daytime > '2008-12-21' and daytime<='2008-12-21 23:59:59'DATE_FORMAT(daytime,'%Y-%m-%d %H时') Ptype dinggou tuiding
2008-12-21 00时 135000000000000000704 0 1
2008-12-21 08时 135000000000000000704 1 0
2008-12-21 09时 135000000000000000704 1 2
2008-12-21 10时 135000000000000000704 1 0
2008-12-21 11时 135000000000000000704 1 1
2008-12-21 12时 135000000000000000704 1 0
2008-12-21 13时 135000000000000000704 1 1
2008-12-21 14时 135000000000000000704 3 1
2008-12-21 15时 135000000000000000704 2 1
2008-12-21 16时 135000000000000000704 3 2
2008-12-21 17时 135000000000000000704 6 1
2008-12-21 18时 135000000000000000704 2 2
2008-12-21 19时 135000000000000000704 1 0
2008-12-21 21时 135000000000000000704 1 0
2008-12-21 22时 135000000000000000704 1 1
2008-12-21 23时 135000000000000000704 0 1
先实现没有数据的小时数据用0补上,实现0-23小时的数据都有
from table where daytime > '2008-12-21' and daytime<='2008-12-21 23:59:59'DATE_FORMAT(daytime,'%Y-%m-%d %H时') Ptype dinggou tuiding
2008-12-21 00时 135000000000000000704 0 1
2008-12-21 08时 135000000000000000704 1 0
2008-12-21 09时 135000000000000000704 1 2
2008-12-21 10时 135000000000000000704 1 0
2008-12-21 11时 135000000000000000704 1 1
2008-12-21 12时 135000000000000000704 1 0
2008-12-21 13时 135000000000000000704 1 1
2008-12-21 14时 135000000000000000704 3 1
2008-12-21 15时 135000000000000000704 2 1
2008-12-21 16时 135000000000000000704 3 2
2008-12-21 17时 135000000000000000704 6 1
2008-12-21 18时 135000000000000000704 2 2
2008-12-21 19时 135000000000000000704 1 0
2008-12-21 21时 135000000000000000704 1 0
2008-12-21 22时 135000000000000000704 1 1
2008-12-21 23时 135000000000000000704 0 1
先实现没有数据的小时数据用0补上,实现0-23小时的数据都有
UNION
SELECT CAST(CONCAT(DATE('2008-12-21 23:59:59'),' 00:01:00') AS DATETIME)or
用一张表保存01-24
再与工作表连接'2008-12-21 23:59:59'转换为你的字段名
数据就是下边这样存储的
daytime Ptype dinggou tuiding
2008-12-21 00 135000000000000000704 0 1
2008-12-21 08 135000000000000000704 1 0
2008-12-21 09 135000000000000000704 1 2
2008-12-21 10 135000000000000000704 1 0
2008-12-21 11 135000000000000000704 1 1
2008-12-21 12 135000000000000000704 1 0
2008-12-21 13 135000000000000000704 1 1 可以用查询语句实现么?
union
SELECT CAST(CONCAT(DATE(daytime),' 00:00:00') AS DATETIME),0,0,0
UNION
SELECT CAST(CONCAT(DATE(daytime'),' 01:00:00') AS DATETIME),0,0,0
......
UNION
SELECT CAST(CONCAT(DATE(daytime'),' 24:00:00') AS DATETIME),0,0,0
但如果不固定,比如查一年,或一个月,或者一周的,则没什么好办法。一般是通过程序来存储过程来实现这样效率上尚可,
比如查询0点和8点有数据,我就要union其他几个小时,但是下次查询有可能5点和23点有数据。。
select DATE_FORMAT(daytime,'%Y-%m-%d %H时'), Ptype,dinggou,tuiding
from table
where date(daytime)='2008-12-21')
union all
select concat( '2008-12-21',' ',mid(100+h,2),'时'),0,0,0
from (
select 0 as h union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23
) t
where t.h not in (select hour(daytime) from table where date(daytime)='2008-12-21')
+------+---------+--------+
| id | content | parent |
+------+---------+--------+
| 1 | AAAAAA | NULL |
| 2 | BBBBBB | NULL |
| 3 | cccccc | 2 |
+------+---------+--------+
3 rows in set (0.00 sec)mysql> select if(parent!='NULL',parent,'') from aa;
+------------------------------+
| if(parent!='NULL',parent,'') |
+------------------------------+
| |
| |
| 2 |
+------------------------------+
3 rows in set, 1 warning (0.00 sec)
if 过滤下