mysql 每天的数据_mysql 统计一周每天得数据

SELECT

a.click_date Time,

IFNULL(b.total,0) as InCome

FROM

(

SELECT

DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 5 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 4 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 3 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 2 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) AS click_date UNION ALL

SELECT

DATE_SUB( CURDATE( ), INTERVAL 0 DAY ) AS click_date

) a

LEFT JOIN (

SELECT

DATE_FORMAT( transaction_time, '%Y-%m-%d' ) AS datetime,

sum( amount ) AS total

FROM

trade

WHERE

bvent_id = "aa740000-0047-5254-aee4-08d727960021"

AND transaction_type = "Revenue"

AND transaction_status = "2"

AND is_deleted = 0

AND transaction_time < CURDATE( ) + 1 AND transaction_time >= DATE_SUB( CURDATE( ), INTERVAL 7 DAY )

GROUP BY

DATE_FORMAT( transaction_time, '%Y-%m-%d' )

) b ON a.click_date = b.datetime

order by a.click_date desc