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
原文:https://www.cnblogs.com/yxzs/p/12191061.html