mysql图表统计，补全日期

最新推荐文章于 2023-05-29 20:28:30 发布

weixin_33948416

最新推荐文章于 2023-05-29 20:28:30 发布

阅读量378

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3568600/blog/3011323

版权

2019独角兽企业重金招聘Python工程师标准>>>

SELECT
    od.date,
    SUM(od.money)
FROM
    (
        SELECT
            SUM(money) money,
            DATE_FORMAT(create_time, '%Y-%m-%d') date
        FROM
            red_packet_number
        WHERE
            create_time > '2019-1-01'
        AND create_time < '2019-1-31'
        GROUP BY
            DATE_FORMAT(create_time, '%Y-%m-%d')
        UNION    (SELECT 0, '2019-01-01')
        UNION    (SELECT 0, '2019-01-02')
        UNION    (SELECT 0, '2019-01-03')
        UNION    (SELECT 0, '2019-01-04')
        UNION    (SELECT 0, '2019-01-05')
        UNION    (SELECT 0, '2019-01-06')
        UNION    (SELECT 0, '2019-01-07')
        UNION    (SELECT 0, '2019-01-08')
        UNION    (SELECT 0, '2019-01-09')
        UNION    (SELECT 0, '2019-01-10')
        UNION    (SELECT 0, '2019-01-11')
        UNION    (SELECT 0, '2019-01-12')
        UNION    (SELECT 0, '2019-01-13')
        UNION    (SELECT 0, '2019-01-14')
        UNION    (SELECT 0, '2019-01-15')
        UNION    (SELECT 0, '2019-01-16')
        UNION    (SELECT 0, '2019-01-17')
        UNION    (SELECT 0, '2019-01-18')
        UNION    (SELECT 0, '2019-01-19')
        UNION    (SELECT 0, '2019-01-20')
        UNION    (SELECT 0, '2019-01-21')
        UNION    (SELECT 0, '2019-01-22')
        UNION    (SELECT 0, '2019-01-23')
        UNION    (SELECT 0, '2019-01-24')
        UNION    (SELECT 0, '2019-01-25')
        UNION    (SELECT 0, '2019-01-26')
        UNION    (SELECT 0, '2019-01-27')
        UNION    (SELECT 0, '2019-01-28')
        UNION    (SELECT 0, '2019-01-29')
        UNION    (SELECT 0, '2019-01-30')
        UNION    (SELECT 0, '2019-01-31')
    ) AS od GROUP BY od.date order by od.date

select sum(money) from red_packet_number where TO_DAYS(create_time) = TO_DAYS("2019-01-30");

转载于:https://my.oschina.net/u/3568600/blog/3011323