在日常需求开发中,经常会遇到统计数据的情况,如统计最近6个月的数据营收情况,或者统计最近6年的,或者是统计某一年12个月的数据情况
其实在遇到这样的需求时都是类似的, 主要看的是sql怎么写 ,其实sql 就分为两部分,一部分是时间列表,一部分是数据列表 ,两张根据日期表相连则可以完成业务。
在这里,数据列表就不做过多探讨,根据实际业务只需按日期分组即可。
那主要的就是日期列表该如何获取
下面展示一个遇到过之前其他同时写的sql
SELECT
yy.d AS date_day,
IFNULL( nn.num, 0 ) AS number
FROM
(
SELECT
1 AS d 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 UNION ALL
SELECT
10 UNION ALL
SELECT
11 UNION ALL
SELECT
12
) yy
LEFT JOIN (
数据列表) nn
ON
yy.d = nn.mon GROUP BY date_day
可以看到使用union all 的形式获取日期列表
这样虽然直观,但是却并不雅观。下面介绍一种使用变量的方法获取日期列表
select @day := (@day +1) as month from (
select @day :=0 from mysql.help_topic limit 12
) a
执行结果如下
分析一下这个sql
首先看:select @day :=0 from mysql.help_topic limit 12
是创建了一个变量并给赋值为0 这里的mysql.help_topic是数据库的系统表 实际上这里是数据库中的任意一张表都可以,limit12 是为了获取12行数据
看一下执行结果:
再看外面部分:select @day := (@day +1)
很名显是每次变量递增加一,所以最后得到的结果就为1到12 。是不是比上面的UNION ALL 看着舒服多了。
下面再看一个是获取最近12个月的sql
SELECT
date_format( @lastDay := last_day( date_add( @lastDay, INTERVAL 1 MONTH ) ), '%Y-%m' ) lastDays
FROM
( SELECT @lastDay := date_add( now(), INTERVAL - 12 MONTH ) FROM mysql.help_topic LIMIT 12 ) a
看下执行效果:
上面的sql 虽然看着比较复杂但是实际理念是一样的
SELECT @lastDay := date_add( now(), INTERVAL - 12 MONTH ) 是创建变量 并赋值为 当前时间减去12个月 ,再limit 12 获取12行
外面@lastDay := last_day( date_add( @lastDay, INTERVAL 1 MONTH ) )
是将lastday变量每次加上1 获取下一个月 ,12次就到了当月,就获取到了最近十二个月的数据
举一反三,近6年 ,近30天 都可以用这样的方法,甚至以及可能需要递增递减的数据集合也可以用这样的方法。
今天的技巧就分享到这里。