台湾报表sql，月度与年度汇总-优快云博客

本文链接：https://blog.youkuaiyun.com/hwq293/article/details/80135186

本文介绍了一个复杂的SQL查询案例，该查询用于从多个表中提取员工的休假余额信息，包括年假、病假等多种类型的假期余额，并通过内连接将这些信息与员工基本信息相结合。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select psn.pk_group,psn.pk_psndoc,psn.code,psn.name,psn.glbdef1,bal.thisyearyial,bal.lastal,bal.lastbx,bal.yxhunjia,
bal.yxsangjia,bal.yxgongshang,bal.yxchanjia,bal.yxchanjian,bal.yxpeichan,bal.yxshijia,bal.yxbingjia,bal.yxnianjia,
bal.jynianjia,bal.jybuxiu,ld1.annualleave,ld1.absenceleave,ld1.sickleave,ld1.compensatedleave
 from  bd_psndoc psn
inner join (select tb.pk_org,tb.pk_psndoc,tb.curyear,
sum(case when tb.pk_timeitem='1002Z710000000021ZLJ' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as thisyearyial,
sum(case when tb.pk_timeitem='1002Z710000000021ZLJ' then tb.lastdayorhour else 0 end ) as lastal,
sum(case when tb.pk_timeitem='10018G1000000000BM2L' then tb.lastdayorhour else 0 end ) as lastbx,--上年得补休，需要获取上一年度得
sum(case when tb.pk_timeitem='1002Z710000000021ZLF' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxhunjia,
sum(case when tb.pk_timeitem='1002Z710000000021ZLN' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxsangjia,
sum(case when tb.pk_timeitem='1002Z710000000021ZLP' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxgongshang,
sum(case when tb.pk_timeitem='1002Z710000000021ZLH' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxchanjia,
sum(case when tb.pk_timeitem='10018G1000000000IOLU' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxchanjian,
sum(case when tb.pk_timeitem='10018G1000000000BM2X' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxpeichan,
sum(case when tb.pk_timeitem='1002Z710000000021ZLB' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxshijia,
sum(case when tb.pk_timeitem='1002Z710000000021ZLD' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxbingjia,
sum(case when tb.pk_timeitem='1002Z710000000021ZLJ' then tb.yidayorhour+tb.freezedayorhour else 0 end ) as yxnianjia,
sum(case when tb.pk_timeitem='1002Z710000000021ZLJ' then tb.curdayorhour-tb.yidayorhour-tb.freezedayorhour else 0 end ) as jynianjia,
sum(case when tb.pk_timeitem='10018G1000000000BM2L' then tb.realdayorhour-tb.yidayorhour-tb.freezedayorhour else 0 end ) as jybuxiu
from  tbm_leavebalance tb group by tb.pk_org,tb.pk_psndoc,tb.curyear,tb.curmonth)bal
on  psn.pk_psndoc=bal.pk_psndoc and psn.pk_org=bal.pk_org and bal.curyear='2018'  --and  bal.pk_timeitem='1002Z710000000021ZLJ'
inner join (select ld.PK_PSNDOC as pk_psnodc,sum(ld.al) as annualleave,
sum(ld.shijia) as absenceleave,
sum(ld.bingjia) as  sickleave,
sum(ld.buxiu) as compensatedleave
 from leave_daily ld where substr(ld.CALENDAR,0,7)='2018-01' group by ld.PK_PSNDOC) ld1
on ld1.pk_psnodc=psn.pk_psndoc
where psn.pk_org='00016H10000000000BIV'