sql显示12个月数据

插入测试数据，数据截至到今年的7月份

<span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">111</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">2</span>,<span style="color: #800000; font-weight: bold;">222</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">3</span>,<span style="color: #800000; font-weight: bold;">333</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">4</span>,<span style="color: #800000; font-weight: bold;">444</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">555</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">6</span>,<span style="color: #800000; font-weight: bold;">666</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">cBudget</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">VALUES</span>(<span style="color: #800000; font-weight: bold;">7</span>,<span style="color: #800000; font-weight: bold;">777</span>)

问题及解决方案

常规的写法直接select，F5执行，Oh My God~~~,显示的7月份的数据，绑定报表插件也只显示到7月份，和需求完全不符啊。

不要着急，下面就是master..spt_values发挥的时候了（当然，你也可以自己建一个存储1-12个数字的临时表），1到12月份的数据出来了

<span style="color: #0000ff;">select</span> b.<span style="color: #0000ff;">number</span> <span style="color: #0000ff;">as</span> <span style="color: #ff00ff;">Month</span>,<span style="color: #ff00ff;">ISNULL</span>(a.Sales,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #0000ff;">as</span><span style="color: #000000;"> Sales
</span><span style="color: #0000ff;">from</span> cbudget <span style="color: #0000ff;">as</span> a <span style="color: #808080;">right</span> <span style="color: #808080;">join</span><span style="color: #000000;">
    (    
    </span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">number</span> <span style="color: #0000ff;">from</span> master.dbo.spt_values <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T1
    </span><span style="color: #0000ff;">WHERE</span> T1.type <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> T1.<span style="color: #0000ff;">number</span> <span style="color: #808080;">between</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">and</span> <span style="color: #800000; font-weight: bold;">12</span><span style="color: #000000;">
     ) b
</span><span style="color: #0000ff;">on</span> b.<span style="color: #0000ff;">number</span><span style="color: #808080;">=</span>a.<span style="color: #ff00ff;">month</span>

大致的原理就是这样，更为复杂的sql语句需要自己来变通了。

sql显示12个月数据

标签：