时间查询

SELECT a.item,IFNULL(b.value,0) AS value, b.water

FROM (

SELECT CURDATE() AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS item

) a LEFT JOIN (

SELECT DATE(startTime) AS date, count(date_format(startTime,'%Y-%m-%d')) AS value ,AVG(water) as water

FROM cwenergyconsumption

GROUP BY DATE(startTime)

) b ON a.item = b.date;

--- 

SELECT a.item,IFNULL(b.value,0) AS value

FROM (

SELECT CURDATE() AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS item

UNION ALL

SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS item

) a LEFT JOIN (

SELECT DATE(startTime) AS date, AVG(water) as value

FROM cwenergyconsumption

GROUP BY DATE(startTime)

) b ON a.item = b.date;

----- 最近一周的数据分类

SELECT 

		name as name , count(name) as value

FROM cwbasicservice

WHERE

flag = 1

AND

DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(createTime)

GROUP BY name

-- 今天一天

 select *

 from eporder

 where 

 to_days(eporder.createTime) = to_days(now())

-- 本月

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)