右连接问题

[get_D]

declare @date datetime

select @date=Date from AttendanceCalendar where
datepart(yyyy,AttendanceCalendar.Date)=YEAR(GETDATE()) and datepart(MM,AttendanceCalendar.Date)=MONTH(GETDATE()) and datepart(DD,AttendanceCalendar.Date)=DAY(GETDATE()-1)
select ef.所属公司,ef.一级部门,ef.二级部门,ef.三级部门,ef.部门,ef.所属区域,ef.所属厂区,
isnull(HumanResourcePlan.PlanAmount,0) as 计划配置,
isnull(humanResourceplan.直接,0) as 计划直接,
isnull(humanResourceplan.间接,0) as 计划间接,
isnull(count(h.Code),0) as 实际间接,
isnull(count(f.Code),0) as 实际直接,
isnull(SUM(ef.当前在岗员工),0) as 当前在岗员工,
isnull(SUM(ef.新进),0) as 新进,
isnull(SUM(ef.离职),0) as 离职,
case when ef.所属厂区='集团总部' then 1
when ef.所属厂区='东莞事业部' then 2
when ef.所属厂区='汽车事业部' then 3
when ef.所属厂区='五金事业部' then 4
when ef.所属厂区='触点事业部' then 5
when ef.所属厂区='塑料事业部' then 6
when ef.所属厂区='宁波甬友' then 7
when ef.所属厂区='明光三友' then 8
when ef.所属厂区='明光潘村三友' then 9
when ef.所属厂区='明光万佳' then 10
when ef.所属厂区='砀山万佳' then 11
when ef.所属厂区='景德镇三友' then 12
end as 级别,
case when ef.所属区域='东莞' then 1
when ef.所属区域='宁波' then 2
when ef.所属区域='明光' then 3
when ef.所属区域='砀山' then 4
when ef.所属区域='景德镇' then 5
when ef.所属区域='上海' then 6
when ef.所属区域='国外' then 7

end as 级别,
isnull(SUM(ef.调入),0) as 调入,
isnull(SUM(ef.调出),0) as 调出,
isnull(sum(roll.病假),0) as 病假,
isnull(sum(roll.年假),0) as 年假,
isnull(sum(roll.事假),0) as 事假,
isnull(sum(roll.婚假),0) as 婚假,
isnull(sum(roll.丧假),0) as 丧假,
isnull(sum(roll.产假),0) as 产假,
isnull(sum(roll.工伤假),0) as 工伤假,
isnull(sum(roll.出差),0) as 出差,
isnull(sum(roll.旷工),0) as 旷工,
isnull(sum(roll.实际出勤时数),0) as 实际出勤时数,
isnull(sum(roll.直接实际出勤时数),0) as 直接实际出勤时数,isnull(sum(roll.间接实际出勤时数),0) as 间接实际出勤时数,isnull(case when SUM(ef.当前在岗员工)>0 then sum(roll.实际出勤时数)/SUM(ef.当前在岗员工) else 0 end ,0) as 出勤率,
isnull(SUM(ef.当前在岗员工),0)-isnull(HumanResourcePlan.PlanAmount,0) as 配置预实对比,
CONVERT(varchar(10),GETDATE()-1,120) as 日期,
DATENAME(DW,GETDATE()-1) as 星期,
(select Name from AttendanceCalendar a,AttendanceHolidayType b where a.AttendanceHolidayTypeId=b.AttendanceHolidayTypeId and date =dateadd(day,-1,(convert(varchar(4),YEAR(GETDATE()))+'-'+convert(varchar(4),month(GETDATE()))+'-'+convert(varchar(4),day(GETDATE())))) and a.CorporationId='1C91E26E-9B75-40C6-B1E7-485B2E686FA9') as 节假日类型,
ISNULL(SUM(gongxiu.公休),0) as 公休




from

(select
distinct
Corporation.Name as 所属公司,
case when Department.DeptLevel<3 then null else Department.Name end as 部门,
case when Department.DeptLevel=1 then Department.Name
when Department.DeptLevel=2 then dep2.Name
when Department.DeptLevel=3 then dep3.Name
when Department.DeptLevel=4 then dep4.Name
when Department.DeptLevel=5 then dep5.Name else null end as 一级部门 ,
case when Department.DeptLevel=1 then null
when Department.DeptLevel=2 then Department.Name
when Department.DeptLevel=3 then dep2.Name
when Department.DeptLevel=4 then dep3.Name
when Department.DeptLevel=5 then dep4.Name else null end as 二级部门 ,
case when Department.DeptLevel=1 then null
when Department.DeptLevel=2 then null
when Department.DeptLevel=3 then Department.Name
when Department.DeptLevel=4 then dep2.Name
when Department.DeptLevel=5 then dep3.Name else null end as 三级部门 ,

Department.DepartmentId as 部门主键,
--job.Name as 职位,
District.Name as 所属区域,
a.ScName as 所属厂区,

--HumanResourcePlan.PlanAmount as 计划配置,
employee.code,employee.employeeid,
case when employee.Date<=@date and employee.LastWorkDate>@date then 1 else 0 end as 当前在岗员工,
case when employee.Date=@date then 1 else 0 end as 新进,
case when employee.LastWorkDate=@date then 1 else 0 end as 离职,
case when t.TranslationDate=@date and t.NewJobId=employee.JobId then 1 else 0 end as 调入,
case when t.TranslationDate=@date and t.OldJobId=employee.JobId then 1 else 0 end as 调出 from employee
left join EmployeeTranslation t on t.EmployeeId=employee.EmployeeId
left join Department on Department.DepartmentId=Employee.DepartmentId
left join Department dep2 on dep2.DepartmentId=Department.ParentId
left join Department dep3 on dep3.DepartmentId=dep2.ParentId
left join Department dep4 on dep4.DepartmentId=dep3.ParentId
left join Department dep5 on dep5.DepartmentId=dep4.ParentId
left join Corporation on Department.CorporationId=Corporation.CorporationId
--left join HumanResourcePlan on HumanResourcePlan.DepartmentId=Employee.DepartmentId
--left join FiscalYear on HumanResourcePlan.FiscalYearId=FiscalYear.FiscalYearId
left join District on Employee.AreaId=District.DistrictId
left join CodeInfo a on Employee.FactoryId=a.CodeInfoId

where Employee.LastWorkDate>=@date
-- and (FiscalYear.Year=YEAR(@date) or HumanResourcePlan.FiscalYearId is NULL)
--and (SUBSTRING(HumanResourcePlan.Month,8,2)=SUBSTRING(CONVERT(nvarchar(20),@date,120),6,2) or HumanResourcePlan.Month is NULL)
) ef
right join
(select HumanResourcePlan.DepartmentId,isnull(sum(case when CodeInfo.ScName like '直接%' then HumanResourcePlan.PlanAmount end),0) 直接,
isnull(sum(case when CodeInfo.ScName like '间接%' then HumanResourcePlan.PlanAmount end),0) 间接,
SUM(HumanResourcePlan.PlanAmount) PlanAmount
from HumanResourcePlan
left join FiscalYear on HumanResourcePlan.FiscalYearId=FiscalYear.FiscalYearId
left join CodeInfo on CodeInfo.CodeInfoId=HumanResourcePlan.XCostType
left join Department on Department.DepartmentId=HumanResourcePlan.DepartmentId
where FiscalYear.Year=YEAR(GETDATE())
and SUBSTRING(HumanResourcePlan.Month,8,2)=SUBSTRING(CONVERT(nvarchar(20),GETDATE()-1,120),6,2) and HumanResourcePlan.CorporationId=Department.CorporationId
--and humanresourceplan.DepartmentId='565F7004-49FF-4569-9D54-D72920D34921'
group by HumanResourcePlan.DepartmentId) HumanResourcePlan
on HumanResourcePlan.DepartmentId=ef.部门主键
left join(select Code,DepartmentId,EmployeeId from Employee a,CodeInfo
where CodeInfo.CodeInfoId=a.ZhiJian and ScName like '间接%' and a.EmployeeStateId in('EmployeeState1001','EmployeeState2001')
) h on h.DepartmentId=ef.部门主键 and h.EmployeeId=ef.EmployeeId
left join(select Code,DepartmentId,EmployeeId from Employee a,CodeInfo
where CodeInfo.CodeInfoId=a.ZhiJian and ScName like '直接%' and a.EmployeeStateId in('EmployeeState1001','EmployeeState2001')
) f on f.DepartmentId=ef.部门主键 and f.EmployeeId=ef.EmployeeId

left join
(select roll.EmployeeId,
isnull(cast(sum(case when roll.AttendanceTypeId='402' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 病假,
isnull(cast(sum(case when roll.AttendanceTypeId='401' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 年假,
isnull(cast(sum(case when roll.AttendanceTypeId='403' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 事假,
isnull(cast(sum(case when roll.AttendanceTypeId='404' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 婚假,
isnull(cast(sum(case when roll.AttendanceTypeId='405' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 丧假,
isnull(cast(sum(case when roll.AttendanceTypeId='17310905db346f21d46e4acdce6c79b8998f5' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 产假,
isnull(cast(sum(case when roll.AttendanceTypeId='17706bf905c501426493b971a5fa72fa9ba5e' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 工伤假,
isnull(cast(sum(case when roll.AttendanceTypeId IN ('701' ,'1762996b21edef6f147cc8db2277ab51787de') and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 出差,
isnull(cast(sum(case when (roll.AttendanceTypeId in ('301','302') and roll.Hours>=240) then 1 end) as decimal(12,2)),0) as 旷工,
isnull(cast(sum(case when roll.AttendanceTypeId like '%R%' and roll.Hours>=240 then 1 end) as decimal(12,2)),0) as 实际出勤时数,
isnull(cast(sum(case when roll.AttendanceTypeId like '%R%' and roll.Hours>=240 and Employee.ZhiJian='ZhiJian_001' then 1 end) as decimal(12,2)),0) as 直接实际出勤时数,
isnull(cast(sum(case when roll.AttendanceTypeId like '%R%' and roll.Hours>=240 and Employee.ZhiJian in('ZhiJian_002','ZhiJian_003','ZhiJian_004','ZhiJian_005','ZhiJian_006') then 1 end) as decimal(12,2)),0) as 间接实际出勤时数
from
AttendanceRollcall roll,Employee
where roll.Date=@date and roll.EmployeeId=Employee.EmployeeId group by roll.EmployeeId) roll
on roll.EmployeeId=ef.EmployeeId
left join (select emprank.EmployeeId,
ISNULL(CAST(SUM(case when emprank.AttendanceHolidayTypeId in ('DefaultHolidayType003','DefaultHolidayType004') then 1 end ) as decimal(12,2)),0) as 公休
from AttendanceEmpRank emprank where emprank.Date=@date group by emprank.EmployeeId) gongxiu on gongxiu.EmployeeId=ef.EmployeeId
left join Employee on Employee.EmployeeId=ef.EmployeeId WHERE (1=1) group by ef.所属公司,ef.一级部门,ef.二级部门,ef.三级部门,ef.部门,ef.所属区域,ef.所属厂区,HumanResourcePlan.PlanAmount,HumanResourcePlan.直接,HumanResourcePlan.间接 order by ef.所属公司,ef.所属厂区,ef.一级部门,ef.二级部门,ef.三级部门,ef.部门,ef.所属区域,HumanResourcePlan.PlanAmount




上面是sql，下面是查询结果，为什么下面那些值是空的呢，我坐的右连接，求大神指导

[get_D]

引用 1 楼 wmxcn2000 的回复:

楼主，你把问题简化一下，先用一些模拟数据。

简单来说就是有的部门内的职位有编制，没人在职，有的部门内没有编制但是有人在职

[砸死牛顿的苹果]

还是举例吧,无关的列 不需要列出来 数据量也可以少一些

[二月十六]

太长了。。看不清楚

[卖水果的net]

楼主，你把问题简化一下，先用一些模拟数据。

[zbdzjx]

语句简化一下，select 后面不要那么多的列，括号中的子查询，也不用那么多列，只要那些必须的列，SQL语句就容易看了。 结果中，“计划配置”那列不为null，看SQL语句，是从HumanResourcePlan这个表或别名得到的，所以，应该是HumanResourcePlan这个表或别名中的记录有问题，在其他表中没有对应的资料。

[qq_32052883]

临时表啊， 写的太复杂了.

[等待戈多12]

你说的1到11行前面那些列为空吗？你那些是左表的，右连接结果为空，可能是有些不满足连接条件的区域存在数据。