天分组统计"/>
mysql sql 按天分组统计
mysql数据库,当我们按日期展示数据时,经常碰到日期时间段内某一天数据不存在,为空。这时候我们查询数据往如下图一样,时间数据发生间断,造成数据缺失,无法连续展示
所以可以加上下面的代码,进行一个链接查询,以时间为链接条件,可以将间断数据补全
SELECT
DATE_FORMAT(
CURDATE( ) - INTERVAL ( a.n + ( 10 * b.n ) + ( 100 * c.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
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 c
WHERE
(
CURDATE( ) - INTERVAL ( a.n + ( 10 * b.n ) + ( 100 * c.n ) ) DAY
) > ( CURDATE( ) - INTERVAL 24 MONTH )
以该语句进行连接查询,如图
MySQL实现按天分组统计,提供完整日期列表,无数据自动补0
SELECT
DATE_FORMAT(CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH, '%Y%m') 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 (CURDATE() - INTERVAL (a.n + (10 * b.n)) MONTH) > (CURDATE() - INTERVAL 24 MONTH)
MySQL实现按月分组统计,提供完整日期列表,无数据自动补0
上述是俩个例子,大家可以根据具体情况自由组合发挥。上述是目前楼主发现的方法,可能还有其他更好方法,希望可以分享给楼主我,谢谢
更多推荐
mysql sql 按天分组统计
发布评论