Sql Serverms SQL按月统计所有部门案例(代码)
以订单统计为例,前端展示柱状图(jquery统计):
表及主要字段描述如下;表名:orders
1.日期createtime
2.金额amount
3.用户userid
情况一:
根据部门统计某一年每月销量(查询一个部门月统计)
1)直接在sql语句中判断每月信息,好处,前台直接调用;坏处,性能不高。
sql语句:
select sum(case when month(s.createtime) = 1 then s.amount else 0 end) as '一月', sum(case when month(s.createtime) = 2 then s.amount else 0 end) as '二月', sum(case when month(s.createtime) = 3 then s.amount else 0 end) as '三月', sum(case when month(s.createtime) = 4 then s.amount else 0 end) as '四月', sum(case when month(s.createtime) = 5 then s.amount else 0 end) as '五月', sum(case when month(s.createtime) = 6 then s.amount else 0 end) as '六月', sum(case when month(s.createtime) = 7 then s.amount else 0 end) as '七月', sum(case when month(s.createtime) = 8 then s.amount else 0 end) as '八月', sum(case when month(s.createtime) = 9 then s.amount else 0 end) as '九月', sum(case when month(s.createtime) = 10 then s.amount else 0 end) as '十月', sum(case when month(s.createtime) = 11 then s.amount else 0 end) as '十一月', sum(case when month(s.createtime) = 12 then s.amount else 0 end) as '十二月' from orders as s where year(s.createtime) = 2014
--其他条件
结果:
一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 741327.00 120505.00 0.00
2)统计出里有值的月份,再前端逻辑判断其他月份补0
sql语句:
select userid, month ( createtime ) as 月份, sum( amount ) as 统计 from orders where year ( createtime ) = 2014 -- 这里假设你要查 2014年的每月的统计。 --其他条件 group by userid, month ( createtime ) 结果: 月份 销售额 10 741327.00 11 120505.00
情况二:
统计所有部门某一年每月销量
1)此数据量大的话影响性能,sql语句(这里未联查部门表):
select userid, sum(case when month(s.createtime) = 1 then s.amount else 0 end) as '一月', sum(case when month(s.createtime) = 2 then s.amount else 0 end) as '二月', sum(case when month(s.createtime) = 3 then s.amount else 0 end) as '三月', sum(case when month(s.createtime) = 4 then s.amount else 0 end) as '四月', sum(case when month(s.createtime) = 5 then s.amount else 0 end) as '五月', sum(case when month(s.createtime) = 6 then s.amount else 0 end) as '六月', sum(case when month(s.createtime) = 7 then s.amount else 0 end) as '七月', sum(case when month(s.createtime) = 8 then s.amount else 0 end) as '八月', sum(case when month(s.createtime) = 9 then s.amount else 0 end) as '九月', sum(case when month(s.createtime) = 10 then s.amount else 0 end) as '十月', sum(case when month(s.createtime) = 11 then s.amount else 0 end) as '十一月', sum(case when month(s.createtime) = 12 then s.amount else 0 end) as '十二月' from orders as s where year(s.createtime) = 2014 group by userid
结果:
userid 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 53495.00 0.00 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 738862.00 37968.00 0.00 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2099.00 22849.00 0.00 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 366.00 0.00 0.00 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6193.00 0.00
2)百度看到有人提到列转行,未看到实例,不太清楚具体实现方式。有知道的朋友,请告知,谢谢!
select userid, month ( createtime ) as 月份, sum( amount ) as 统计 from orders where year ( createtime ) = 2014 -- 这里假设你要查 2014年的每月的统计。 group by userid,month ( createtime ) 结果: userid 月份 统计 1 10 738862.00 2 10 2099.00 3 10 366.00 4 11 53495.00 1 11 37968.00 2 11 22849.00
5 11 6193.00
最后一个例子: case分类显示信息与根据属性值关联查询不同表中信息
也就是根据一个字段的值关联查询不同的表。需求是:根据employeeexam的employeetype (0,代表内部员工,1代表外部员工)查询对应的内部员工表或者外部员工表中的性别,同时根据employeeexam的employeetype查询出部门表的员工所属部门姓名(由employeeexam表关联员工表,员工表关联部门表)。
select g.employeename, case g.employeetype when '0' then(select sex from employee_in where idcode=g.employeeid) else (select sex from employee_out where idcode=g.employeeid) end sex, g.employeeid, g.exammethod, (case g.employeetype when '0' then '内部员工' when '1' then '外部员工' else '空' end)employeetype, case g.employeetype when '0' then(select department.departmentname from employee_in,department where idcode=g.employeeid and department.departmentid=employee_in.departmentid ) else (select unit.name from unit,employee_out where idcode=g.employeeid and employee_out.unitid=unit.unitid ) end departmentname, case g.employeetype when '0' then(select employee_in.trainstatus from employee_in where idcode=g.employeeid) else (select employee_out.trainstatus from employee_out where idcode=g.employeeid) end trainsuatus from employeeexam g
解析:查询性别:sex 如果employeeexam.employeetype为0,查询employee_in表中对应员工性别;如果employeeexam.employeetype为1,查询employee_out 表中对应员工性别;
查询员工类型:employeetype 如果是0代表是内部员工,如果是1代表是外部员工,其他的话是空。
查询员工部门名字:departmentname 如果employeeexam.employeetype为0,查询department表中的departmentname (根据employeeexam.idcode=g.employeeid and department.departmentid=employee_in.departmentid);如果employeeexam.employeetype为1,查询unit表中的name。
查询情况:trainsuatus 类似于sex
结果:
上一篇: Redmine之报表应用研究 F#CC++C#D语言
下一篇: Redmine 中文帮助 和演示程序