weixin_42684304
2021-08-05 10:57
采纳率: 0%
浏览 129

sql server已完成当前时间的数据查询,求去年当前时间的同比该如何写?

手上有这么一个sql server数据库表:
img

需要查询获取以下格式的信息,该如何写一段代码呢?

img

已完成当日当前数据的查询和求和,接下来想怎么实现查询去年同期数据和同比?数据在同一个表中
select isnull(ltrim(EntranceName),'总计')Name, sum(InSum)InSum
from view_table
where CountDate >= cast(GETDATE() as date)
group by Name with rollup
ORDER BY inSum
—— 求获取去年同期数据和同比的代码

  • 点赞
  • 收藏

5条回答 默认 最新

  • 曦陽惜夏 2021-08-05 11:06
    select *
    k1.Name as name ,
    k1.InSum as k1InSum,
    k2.InSum as k2InSum,
    k1.InSum/k2.InSum  as kInSum 比值数据
    
    
    from (
        select isnull(ltrim(EntranceName),'总计')Name, sum(InSum)InSum
        from view_table
        where CountDate >= cast(GETDATE() as date)
        group by Name with rollup
        ORDER BY inSum
    
    ) as k1 left join 
    
    (
    select isnull(ltrim(EntranceName),'总计')Name, sum(InSum)InSum
    from view_table
    where CountDate bettwen '去年的开始时间' and '去年的结束时间'
    group by Name with rollup
    ORDER BY inSum
    
    ) as k2 on k2.name  = k1.name
    
    
    点赞 打赏 评论
  • sinJack 2021-08-05 11:08

    方式有多种。
    方式一:可以通过union的方式将当前数据和去年同期数据求出来,然后作为子查询。外层select 当前数据,去年数据,同比值 from 子查询;
    方式二:直接对查询字段做子查询。select (select ..) 当前数据,(select ..)去年数据, 当前数据/去年数据 from ...

    点赞 打赏 评论
  • weixin_42684304 2021-08-05 15:40

    就是不会查询去年今日的数据...

    点赞 打赏 评论
  • weixin_43941523 2021-08-06 09:49

    select dateadd(year,-1,getdate()) 去年今日的时间

    点赞 打赏 评论
  • __Apollos 2021-08-12 06:59

    部分模拟数据
    img

    查询结果
    img

    查询SQL

    select dview.name,dview.currentsum,bview.lastsum,cast(Convert(decimal(10,4),(dview.currentsum-bview.lastsum)/(bview.lastsum+0.0)*100) as VARCHAR) +'%' yoyvalue from 
    
    (
    select countdate,isnull(ltrim(name),'总计') name,sum(insum) currentsum from (
        select year(countdate) countdate,name,insum  
            from view_table where year(countdate) = year(cast(GETDATE() as date))
    ) cview group by countdate,name with rollup HAVING countdate is not null
    ) dview
    
    left join
    
    (
    select countdate ,isnull(ltrim(name),'总计') name,sum(insum) lastsum from (
        select year(countdate) countdate,name,insum  
            from view_table where year(countdate) = year(DATEADD(yy,-1,cast(GETDATE() as date)))
    ) aview group by countdate,name with rollup HAVING countdate is not null
    ) bview
    
    on  dview.name = bview.name
    

    如果需要更细的时间维度统计同比,自行调整时间即可,方法是一样的
    希望有帮到你

    点赞 打赏 评论

相关推荐 更多相似问题