首页 新闻 搜索 专区 学院

sql汇总问题

0
悬赏园豆:50 [已解决问题] 解决于 2014-06-21 20:21

需要把每个销售员的每月的销售额及全年的销售额都汇总出来,显示成下图,求sql语句

xiaoafei1991的主页 xiaoafei1991 | 菜鸟二级 | 园豆:466
提问于:2014-06-21 17:58
< >
分享
最佳答案
0

图片打不开

收获园豆:50
飞来飞去 | 老鸟四级 |园豆:2057 | 2014-06-21 18:11

可能是网速不好,刷新下页面

xiaoafei1991 | 园豆:466 (菜鸟二级) | 2014-06-21 19:09

@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
飞来飞去 | 园豆:2057 (老鸟四级) | 2014-06-21 19:58

@飞来飞去: 谢谢!

xiaoafei1991 | 园豆:466 (菜鸟二级) | 2014-06-21 20:19
其他回答(1)
0

为了方便对时间进行处理,最好将时间的格式都调整为“2014-MM-DD”的格式,然后利用下面的sql语句分别得到不同月份的销售额(其中需要改变的有salesInMay以及'05'):

select salesperson, sum(sales) as salesInMay from sell where strftime('%m', subtime) = '05' group by salesperson;

最后将结果进行整合,就能够得到最终的结果。

----------------------------------------------------------

希望对你有帮助。

如果有更简单的方法,请站内信一下哈~~

grassofsky | 园豆:342 (菜鸟二级) | 2014-06-21 20:39

好吧,落后了。

支持(0) 反对(0) grassofsky | 园豆:342 (菜鸟二级) | 2014-06-21 20:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册