按某一列里面同一类横向显示数据总数-创新互联

如下图，统计某一时间段请假类别为Kj_tx 积休假、Kj_nj年假的天数综合 并按列显示

10年建站经验， 成都网站设计、成都网站制作客户的见证与正确选择。创新互联建站提供完善的营销型网页建站明细报价表。后期开发更加便捷高效，我们致力于追求更美、更快、更规范。

select 
a.AlEmpID as 员工ID,
a.alsdate,
a.aledate,
AlTime=(
case when a.ALSTime=480 and a.ALETime=1050 then 1 
     when a.ALSTime=480 and a.ALETime=720  then 0.5 
     when a.ALSTime=840  and a.ALETime=1050 then 0.5 
     when a.ALSTime=870  and a.ALETime=1050 then 0.5 
      else 0 end) ,
a.ALFType as 请假类别
from  kq_askleave a 
where a.AlEmpID=734 and a.alsdate>='2018-03-01 00:00:00'

select 
aa.员工ID,
max(d.Dept_lname) as 科室,
max(b.Emp_code) as 工号,
max(b.Emp_name) as 姓名,
max(b.Emp_zhiweiname) as 职位,
isnull(sum(case when  aa.请假类别 ='Kj_nj' then aa.AlTime+AlDay end ),0) 年假,
isnull(sum(case when  aa.请假类别 ='Kj_sj' then aa.AlTime+AlDay end ),0) 事假,
isnull(sum(case when  aa.请假类别 ='Kj_bj' then aa.AlTime+AlDay end ),0) 病假,
isnull(sum(case when  aa.请假类别 ='Kj_cj' then aa.AlTime+AlDay end ),0) 产假,
isnull(sum(case when  aa.请假类别 ='Kj_hj' then aa.AlTime+AlDay end ),0) 婚假,
isnull(sum(case when  aa.请假类别 ='Kj_tx' then aa.AlTime+AlDay end ),0) 积休假
from 
(select 
a.AlEmpID as 员工ID,
a.alsdate,
a.aledate,
AlDay=(
case when a.ALSTime=480 and a.ALETime=1050  then datediff(day,a.alsdate,a.aledate)+1 
     when a.ALSTime=480 and a.ALETime=720  then  datediff(day,a.alsdate,a.aledate)
     when a.ALSTime=840  and a.ALETime=1050 then datediff(day,a.alsdate,a.aledate) 
     when a.ALSTime=870  and a.ALETime=1050 then datediff(day,a.alsdate,a.aledate) 
      else 0 end) ,
AlTime=(
case when a.ALSTime=480 and a.ALETime=1050 then 0 
     when a.ALSTime=480 and a.ALETime=720  then 0.5 
     when a.ALSTime=840  and a.ALETime=1050 then 0.5 
     when a.ALSTime=870  and a.ALETime=1050 then 0.5 
      else 0 end) ,

a.ALFType as 请假类别
from  kq_askleave a 
where a.AlEmpID=734 and a.alsdate>='2016-11-01 00:00:00' and a.aledate<='2018-05-01 00:00:00') aa
left join employeeview b on aa.员工ID=b.emp_id 
left join kq_altype c on aa.请假类别=c.kqaltcode 
left join emp_department d on b.Dept_code=d.Dept_code
group by  aa.员工ID

另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享名称：按某一列里面同一类横向显示数据总数-创新互联
标题网址：http://shouzuofang.com/article/icpci.html