SQL 查询本月无数据用上个月的数据问题

SQL 查询本月无数据用上个月的数据

前言

因为标题有长度限制，先简要说明一下应用场景。比如我们要查一段时间范围内（2013-08-01至2013-12-31）每个月每个运营商的用户总量，每个运营商用户量每个月更新的时间可能不同也可能该月没有数据，如果某个运营商本月用户量没有值，则用上个月的用户量。

下面就给大家一步一步分析，解决该问题。

1.  创建基础的表和示例数据

首先我们需要创建一个用户量表，具体的表结构和表内容如下：

 

2.  分析题目要求

通过对前言中描述的应用场景分析，我们可以得出以下几点讯息：

l  获取一个时间范围内的各个运营商的各个月的数据

l  个别运营商本月无数据的用距离本月日期最近的数据

结合上边的示例数据分析，也就是如果我们要求2013-09-01到2013-12-31之间各个运营商这几个月的数据得到的结果应该是如下图所示：

 

上图中红框标注的部分在数据库中是不存在的，也就是说12月份数据库中没有数据，我们就使用距离12月最近的数据。

3.  逐步解决问题

题目要求的是求一个时间段内的几个月的各运营商的用户总量，这样我们先把问题分解，先求出一个月的各运营商的用户总量。

3.1要求一个月的各运营商的用户总量，我们先求出该月或者据该月最近的用户总量更新的日期。

为了更好的做演示示例，12月份没有数据，我们就用12月份来做演示。

具体代码如下：

select SP_Name as name,MAX(Create_Date) as "group" from UserCount
where Create_Date<'2013-12-31'
group by SP_Name

　　

执行结果如下：

 

3.2求出该月的最近的更新日期后，求出相对应的值.

具体代码如下：

with sub as
(
select SP_Name as name,MAX(Create_Date) as "group" from UserCount
where Create_Date<'2013-12-31'
group by SP_Name
)
  select us.SP_Name as "name",'201312' as "date" ,SUM(us.User_Count) as "value"
  from UserCount us
  right join sub s on s.name=us.SP_Name
  where us.Create_Date=s."group"
 group by us.SP_Name,s."group"

　　

执行结果如下：

 

这样我们就完成的题目所需的求出某个月的用户量，且该月没有值，采用距离该月日期最近的更新日期的值。

3.3 循环求出每个月的用户量，最后将几个月的用户量合并

但是，问题又来了，既然我们求出了某一个月的值，那么如何求出一个时间范围内也就是几个月的用户量呢？

很明显，这需要通过循环分别查出时间范围内每一个月的值，然后将这些值Union就可以了。为了考虑到实用性，我们将这一部分的操作封装在一个函数里。

具体代码如下：

create procedure getMonthData(@str1 varchar(200),@str2 varchar(200))--传入两个日期范围的字符串,形如：20130901，
as
declare @date1 date;--定义两个日期变量，用来将传入的字符串转换为时间，并作为循环的控制条件
declare @date2 date;
begin
set @date1=DATEADD("mm",1,cast(@str1+'01' as date));--处理传入的字符串，转换为时间默认为，
set @date2=DATEADD("mm",1,cast(@str2+'01' as date));
create table #temp1 (name varchar(200),"group" varchar(200),"value" int);--创建一个临时表用来存储每次循环产生的数据，相当于将每个月查询出的数据union
while @date1<=@date2
    begin
    with sub as --求出当前月，用户量最新的更新日期
    (
    select SP_NAME as "name",MAX(Create_Date) as "value"
    from UserCount
    where Create_Date<@date1
    group by SP_NAME
    ),
    sub1 as --求出当前月各运营商的用户总量
    (
    select SP_NAME as "name",@str1 as "group",MAX(User_Count) as "value"
    from UserCount ue
    right join sub s on s."name"=ue.SP_NAME
    group by ue.SP_NAME
    )
    insert into  #temp1 select * from sub1 --将当前月的用户总量插入的临时表保存
    set @date1=dateadd("mm",1,@date1)--更改循环条件
    set @str1=cast(CAST(@str1 as int )+1 as varchar(200))--更改月份
    end
select * from #temp1;--查询临时表，展现所有的数据
end

　　

具体结果如下：

 

这样就基本上上完成的题目的要求了，但是，这还不是最完美的，请注意上边黄色高亮部分，如果日期为参数为201309，201401时，查询出来的结果就是这样的。

 

也就是说在日前跨年的情况下，月份的展示是乱的，这样我们可以再创建一个函数处理这样的问题。

具体代码如下：

create  function modifydate(@date varchar(200))
returns varchar(200)
as
begin
declare @month int;
set @month=cast(SUBSTRING(@date,5,2) as int)
if @month>12
begin
set @date=cast(cast(SUBSTRING(@date,1,4) as int)+@month/12 as varchar) + (case when @month%12=0 then '01' when @month%12<10 then '0'+CAST(@month%12 as varchar)   else CAST(@month%12 as varchar) end)
end
return @date;
end

　　

这样就很好的解决了跨年时显示的日期格式不正确的问题，同时还需要将上边高亮的那一行代码改为如下代码即可：

set @str1=dbo.modifydate(cast(CAST(@str1 as int )+1 as varchar(200)))--更改月份

　

   

    好了，今天就总结到这里了，希望能给大家一些帮助。主要是一个思路吧，之前写过一个同样需求的sql，当时就只是用一个sql语句就实现了，但是时间长了，忘了怎么做的，回头找找，再跟大家分享。