方式一： SELECT T5.PersonalNo AS ‘工号‘,T5.Name AS ‘姓名‘ ,P2.Dept AS ‘部门‘ , T5.dep AS ‘部门‘ FROM PersonPositionLine P2 LEFT JOIN Person P1 on P1.PersonId = P2.PersonId LEFT JOIN ( (SELECT T2.PersonalNo AS ‘PersonalNo‘ ,T2.Names AS ‘Name‘ ,T2.Department AS ‘dep‘ FROM IndividualOvertimeApplicationLine T1 LEFT JOIN IndividualOvertimeApplication T2 on T1.DocEntry=T2.DocEntry) --UNION 可以过滤点重复的数据,UNION ALL 则不过滤 UNION all ( SELECT K1.LinePersonalNo AS ‘PersonalNo‘ , K1.LineName AS ‘Name‘ , K1.LineDepartment AS ‘dep‘ FROM BatchOvertimeApplicationLine K1 LEFT JOIN BatchOvertimeApplication K2 on K1.DocEntry =K2.DocEntry ) --这四张表UNION ALL起来 最好起个别名如T5方便查询，如果UNION的字段名不一样可以起个别名 --cASt(T5.PersonalNo AS int) 把String转化为int )T5 on p1.EmployeeNo= cast(T5.PersonalNo AS int) where T5.PersonalNo !=‘‘ --注:查询的时候最好将数据大的一张表放在前面,如果联立是INNER JOIN 则不需要
方式二： SELECT T2.PersonalNo,T2.Names,T4.Position,T4.CostCenter,T2.Department,T1.LineOvertimeType, T1.LineBeginTime,T1.LineEndTime,T1.LineTotalHours,T1.LineCompensationType FROM IndividualOvertimeApplicationLine T1 LEFT JOIN IndividualOvertimeApplication T2 ON T1.DocEntry=T2.DocEntry JOIN Person T3 ON T3.EmployeeNo=T2.PersonalNo JOIN PersonPositionLine T4 ON T3.PersonId=T4.PersonId WHERE T2.Status=40 AND CONVERT(NVARCHAR(10), T1.LineBeginTime,120) >=‘2015-07-01‘ AND CONVERT(NVARCHAR(10), T1.LineEndTime,120) <=‘2015-07-31‘ UNION ( SELECT T5.LinePersonalNo,T5.LineName,T8.Position,T8.CostCenter,T5.LineDepartment,T6.OvertimeType,T6.BeginTime,T6.EndTime,T6.TotalHours,T6.CompensationType FROM BatchOvertimeApplicationLine T5 LEFT JOIN BatchOvertimeApplication T6 ON T5.DocEntry=T6.DocEntry JOIN Person T7 ON T7.EmployeeNo=T5.LinePersonalNo JOIN PersonPositionLine T8 ON T7.PersonId=T8.PersonId WHERE T6.Status=40 AND CONVERT(NVARCHAR(10), T6.BeginTime,120)>=‘2015-07-01‘ AND CONVERT(NVARCHAR(10), T6.EndTime,120)<=‘2015-07-31‘ )
原文：http://my.oschina.net/isxiaoge/blog/495382