sql汇总问题
0
悬赏园豆：50
[已解决问题]
解决于 2014-06-21 20:21
需要把每个销售员的每月的销售额及全年的销售额都汇总出来，显示成下图,求sql语句
最佳答案
0
图片打不开
收获园豆：50
可能是网速不好，刷新下页面
@xiaoafei1991:
select salesperson as 销售人员, sum(case when month(subtime)=1 then sales else 0 end) as [1月总销售额], sum(case when month(subtime)=2 then sales else 0 end) as [2月总销售额], sum(case when month(subtime)=3 then sales else 0 end) as [3月总销售额], sum(case when month(subtime)=4 then sales else 0 end) as [4月总销售额], sum(case when month(subtime)=5 then sales else 0 end) as [5月总销售额], sum(case when month(subtime)=6 then sales else 0 end) as [6月总销售额], sum(case when month(subtime)=7 then sales else 0 end) as [7月总销售额], sum(case when month(subtime)=8 then sales else 0 end) as [8月总销售额], sum(case when month(subtime)=9 then sales else 0 end) as [9月总销售额], sum(case when month(subtime)=10 then sales else 0 end) as [10月总销售额], sum(case when month(subtime)=11 then sales else 0 end) as [11月总销售额], sum(case when month(subtime)=12 then sales else 0 end) as [12月总销售额], sum(sales) as [全年总销售额] from [table] where year(subtime)=2014 group by salesperson
@飞来飞去: 谢谢！
其他回答(1)
0
为了方便对时间进行处理，最好将时间的格式都调整为“2014-MM-DD”的格式，然后利用下面的sql语句分别得到不同月份的销售额（其中需要改变的有salesInMay以及'05'）：
select salesperson, sum(sales) as salesInMay from sell where strftime('%m', subtime) = '05' group by salesperson;
最后将结果进行整合，就能够得到最终的结果。
----------------------------------------------------------
希望对你有帮助。
如果有更简单的方法，请站内信一下哈～～
好吧，落后了。