sql查询统计，按照时间排序，将没有结果的那天赋值0

箱葙不想

于 2023-06-08 11:26:15 发布

阅读量339

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/hhhhhac/article/details/131104147

版权

   SELECT t.date as title,IFNULL(c.num, 0) AS number FROM (SELECT
            DATE_FORMAT(#{时间} - INTERVAL (a.n + (10 * b.n)) DAY, '%Y-%m-%d') AS date
            FROM (
            SELECT 0 AS n UNION ALL SELECT 1 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
            ) AS a CROSS JOIN (
            SELECT 0 AS n UNION ALL SELECT 1 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
            ) AS b
            WHERE (#{时间} - INTERVAL (a.n + (10 * b.n)) DAY) > (#{时间} - INTERVAL #{天数} DAY) ) t
            LEFT JOIN (
            SELECT  COUNT(1) AS num, DATE(create_time) AS time
            FROM 表名
            GROUP BY DATE(create_time)
            ) c ON DATE(time) = date
        ORDER BY t.date DESC

将0-9两个结果集CROSS JOIN连接，然后用INTERVAL 用于计算两个时间之间的时间差，计算出时间排序的结果集，再连接表查询