欢迎光临
个人知识库,ERP、IT知识分享和应用

Excel / SQL实现按日期累加

参考代码1:根据a.日期,和b表日期比较,遍历(逐条数据查找b.日期)只要是小于等于a.日期的收入数据,就累加求和。

 

<span class="token keyword">select</span> a<span class="token punctuation">.</span>日期<span class="token punctuation">,</span>
<span class="token punctuation">(</span>
<span class="token keyword">select</span> <span class="token function">sum</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>卖茶叶蛋收入<span class="token punctuation">)</span> <span class="token keyword">as</span> 累加 <span class="token keyword">from</span> 
<span class="token punctuation">[</span>sheet1$a1<span class="token punctuation">:</span>b13<span class="token punctuation">]</span> b <span class="token keyword">where</span> b<span class="token punctuation">.</span>日期<span class="token operator"><=</span>a<span class="token punctuation">.</span>日期
<span class="token punctuation">)</span>  <span class="token keyword">as</span> 累加 
<span class="token keyword">from</span>  
<span class="token punctuation">[</span>sheet1$a1<span class="token punctuation">:</span>b13<span class="token punctuation">]</span> a

参考代码2:a.日期对比b表日期,只要是b.日期小于或等于a.日期的数据都分成一组,然后求和.

 

<span class="token keyword">select</span> a<span class="token punctuation">.</span>日期<span class="token punctuation">,</span><span class="token function">sum</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>卖茶叶蛋收入<span class="token punctuation">)</span> <span class="token keyword">as</span> 累加 <span class="token keyword">from</span> 
<span class="token punctuation">[</span>sheet1$a1<span class="token punctuation">:</span>b13<span class="token punctuation">]</span> a 
inner <span class="token keyword">join</span>  
<span class="token punctuation">[</span>sheet1$a1<span class="token punctuation">:</span>b13<span class="token punctuation">]</span> b 
on  
a<span class="token punctuation">.</span>日期<span class="token operator">>=</span>b<span class="token punctuation">.</span>日期  
<span class="token keyword">group</span> by a<span class="token punctuation">.</span>日期
未经允许不得转载:Blog.XiaoMing.Xyz » Excel / SQL实现按日期累加

登录

找回密码

注册