统计24小时，31天，12个月，4个季度。年

统计24小时数据

SELECT

dayHour NAMES,

ITEMNAME,

IF(VALUEA IS NULL, 0, VALUEA) AS VALUEA

FROM (SELECT

VALUEA,

ITEMNAME,

count(VALUEA) AS count,

DATE_FORMAT(CDATE, '%H')

AS hour

FROM pm_cs_save_hour

WHERE UNITID='000000000001' AND ITEMID='E00000000002' AND CDATE between '2018-12-13 00:00:00' and '2018-12-13 23:59:59'

GROUP BY hour

ORDER BY 1) A

RIGHT JOIN (SELECT one.hours + two.hours AS dayHour

FROM (SELECT 0 hours

UNION ALL SELECT 1 hours

UNION ALL SELECT 2 hours

UNION ALL SELECT 3 hours

UNION ALL SELECT 4 hours

UNION ALL SELECT 5 hours

UNION ALL SELECT 6 hours

UNION ALL SELECT 7 hours

UNION ALL SELECT 8 hours

UNION ALL SELECT 9 hours) one

CROSS JOIN (SELECT 0 hours

UNION ALL SELECT 10 hours

UNION ALL SELECT 20 hours) two

WHERE (one.hours + two.hours) < 24) B ON A.hour = CONVERT(B.dayHour, SIGNED)

ORDER BY dayHour

 

 

统计本月数据

select DATE_ADD(curdate(),interval -day(curdate())+dd.eday day) as NAMES,ITEMNAME,IF(VALUEA IS NULL, 0, VALUEA) AS VALUEA

from (

select 1 as eday union select 2 union select 3 union select 4 union select 5

 

union select 6 union select 7 union select 8 union select 9 union select 10

 

union select 11 union select 12 union select 13 union select 14 union select 15

 

union select 16 union select 17 union select 18 union select 19 union select 20

 

union select 21 union select 22 union select 23 union select 24 union select 25

 

union select 26 union select 27 union select 28 union select 29 union select 30

 

union select 31) dd

left join (

 

select DATE_FORMAT(rf.CDATE, '%e') d , DATE_FORMAT(rf.CDATE, '%Y%m%d')s,ITEMNAME, VALUEA from pm_cs_save_day rf

 

where UNITID='000000000001' AND ITEMID='E00000000002' and rf.CDATE>'2018-12-01'

 

and rf.CDATE<'2018-12-31'

 

group by d) tp

 

on tp.d = dd.eday;

 

统计近30天数据

SELECT w.orderDate AS NAMES,res.ITEMNAME,ifnull(res.orderSum,0) AS VALUEA

FROM

(

SELECT DATE_SUB(CURDATE(), INTERVAL m.s-1 DAY) as orderDate from (

SELECT 1 as s 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 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31

)m

)w

LEFT JOIN

(

SELECT COUNT(*) as orderCount,ITEMNAME,VALUEA as orderSum ,date(CDATE) as orderDate

from pm_cs_save_day WHERE date_format(CDATE, '%Y%m') = date_format(curdate() , '%Y%m')

GROUP BY orderDate

)res ON w.orderDate = res.orderDate order by w.orderDate ASC;

统计12个月

 

select dd.eday as NAMES,ITEMNAME,IF(VALUEA IS NULL, 0, VALUEA) AS VALUEA

from (

select 1 as eday union select 2 union select 3 union select 4 union select 5

union select 6 union select 7 union select 8 union select 9 union select 10

union select 11 union select 12 ) dd

left join (

select DATE_FORMAT(rf.CDATE, '%m') d , DATE_FORMAT(rf.CDATE, '%Y-%m')s,ITEMNAME, VALUEA

from pm_cs_save_month rf

where UNITID='000000000001' AND ITEMID='E00000000002' and DATE_FORMAT(rf.CDATE, '%Y-%m') >'2018-01'

and DATE_FORMAT(rf.CDATE, '%Y-%m') <='2018-12'

group by d) tp

on tp.d = dd.eday;

统计季度

 

select dd.eday as NAMES,ITEMNAME,IF(VALUEA IS NULL, 0, VALUEA) AS VALUEA

from (

select 1 as eday union select 2 union select 3 union select 4 ) dd

left join (

 

SELECT QUARTER(CDATE) as d,ITEMNAME, VALUEA FROM pm_cs_save_quarter WHERE YEAR(CDATE) = '2018' AND

 

group by d) tp

on tp.d = dd.eday;

 

 

统计年

select date_format(CDATE, '%Y') as NAMES,ITEMNAME, VALUEA from pm_cs_save_year where YEAR(CDATE) = '2018' AND UNITID='000000000001' AND ITEMID='E00000000002' group by date_format(CDATE, '%Y');