（最好是哪儿位好心人能给我一些学习的资料，我也不想总问大家）
--汇总查询全部信息
select tb3.*,(cast(tb3.年休假天数 as int)-cast(tb3.实休 as int)) as 剩余
from
(select distinct tb1.姓名,tb1.科室,tb1.年休假天数,tb2.*
from
(select * from ciq_Vacation) as tb1,
(select 员工编号,
sum(case when 休假种类='年假' then isnull(实际休假天数,计划休假天数) else 0 end) as 实休,
sum(case when 休假种类='病假' then isnull(实际休假天数,计划休假天数) else 0 end) as 病假,
sum(case when 休假种类='事假' then isnull(实际休假天数,计划休假天数) else 0 end) as 事假,
sum(case when 休假种类='产假' then isnull(实际休假天数,计划休假天数) else 0 end) as 产假,
sum(case when 休假种类='婚假' then isnull(实际休假天数,计划休假天数) else 0 end) as 婚假,
sum(case when 休假种类='丧假' then isnull(实际休假天数,计划休假天数) else 0 end) as 丧假,
sum(case when 休假种类='探望父母' then isnull(实际休假天数,计划休假天数) else 0 end) as 探望父母,
sum(case when 休假种类='探望配偶' then isnull(实际休假天数,计划休假天数) else 0 end) as 探望配偶,
sum(case when 休假种类='其他' then isnull(实际休假天数,计划休假天数) else 0 end) as 其他
from ciq_Vacation
group by 员工编号) as tb2
where tb1.员工编号=tb2.员工编号) tb3
order by tb3.员工编号
