select DATA.*,WFTable.wf_FormID,WFTable.wf_CurrentOwnerName,WFTable.wf_CurrentOwnerID,wf_CurrentStep from (SELECT EF_ID AS ID, EF_ApplyBillNo AS NO, EF_EmpID AS EmpID, EF_EmpNO AS EmpNO, EF_EmpName AS EmpName, EF_Status AS Status, EF_Flag AS Flag,
EF_SubmitEmpID AS SubmitEmpID, EF_SubmitEmpName AS SubmitEmpName, EF_SubmitDate AS SubmitDate,EF_CreatorEmpID as CreatorEmpID, 5 AS FormType,'转正申请单' AS FormTypeName , EF_DeptNO AS DeptNO, EF_DeptName AS DeptName, EF_EmpDomainName as EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_EmployeeFormal
UNION ALL
SELECT EC_ID AS ID, EC_ApplyBillNo AS NO, EC_EmpID AS EmpID, EC_EmpNo AS EmpNO, EC_EmpName AS EmpName, EC_Status AS Status,
EC_Flag AS Flag, EC_SubmitEmpID AS SubmitEmpID, EC_SubmitEmpName AS SubmitEmpName, EC_SubmitDate AS SubmitDate,EC_CreatorEmpID as CreatorEmpID, case ec_billtypeid
when 0 then 6 when 1 then 11 end AS FormType, '人事调整申请单' AS FormTypeName, EC_DeptNo, EC_DeptName, EC_EmpDomainName,
ecm.ECM_EmpNameList,ecm.ECM_EmpNoList
FROM dbo.EDA_EmployeeChange
left join EDA_EmployeeChangeMulti ecm on EDA_EmployeeChange.EC_ID=ecm.ECM_ChangeID
UNION ALL
SELECT ELM_ID AS ID, ELM_ApplyBillNo AS NO, ELM_EmpID AS EmpID, ELM_EmpNO AS EmpNO, ELM_EmpName AS EmpName, ELM_Status AS Status,
ELM_Flag AS Flag, ELM_SubmitEmpID AS SubmitEmpID, ELM_SubmitEmpName AS SubmitEmpName, ELM_SubmitDate AS SubmitDate, ELM_CreatorEmpID as CreatorEmpID,7 AS FormType, '请假申请单' AS FormTypeName,ELM_DeptNO, ELM_DeptName, ELM_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_EmployeeLeaveMain
UNION ALL
SELECT WAM_ID AS ID, WAM_ApplyBillNo AS NO, WAM_EmpID AS EmpID, WAM_EmpNO AS EmpNO, WAM_EmpName AS EmpName, WAM_Status AS Status,
WAM_Flag AS Flag, WAM_SubmitEmpID AS SubmitEmpID, WAM_SubmitEmpName AS SubmitEmpName, WAM_SubmitDate AS SubmitDate,WAM_CreatorEmpID as CreatorEmpID,8 AS FormType, '福利与补贴申请单' AS FormTypeName, WAM_DeptNO, WAM_DeptName, WAM_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_WelfareAndAllowanceMain
UNION ALL
SELECT DC_ID AS ID, DC_ApplyBillNo AS NO, DC_EmpID AS EmpID, DC_EmpNO AS EmpNO, DC_EmpName AS EmpName, DC_Status AS Status,
DC_Flag AS Flag, DC_SubmitEmpID AS SubmitEmpID, DC_SubmitEmpName AS SubmitEmpName, DC_SubmitDate AS SubmitDate,
DC_CreatorEmpID AS CreatorEmpID,10 AS FormType, '组织结构调整申请单' AS FormTypeName, DC_DeptNO, DC_DeptName, DC_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_DepartmentChange)
AS DATA
left Join
(select
wf_FormID,wf_CurrentOwnerName,wf_CurrentOwnerId,wf_CurrentStep,CWF.fmr_ID,wf_VicegerentID
from WF_WorkFlow
AS WF
LEFT JOIN
WF_CfgWorkflow
AS CWF
ON WF.wfc_ID=CWF.wfc_ID
) WFTable
ON DATA.ID = WFTable.wf_formID AND DATA.FORMTYPE=WFTable.fmr_ID
WHERE DATA.FLAG=1
and (
SubmitEmpID=1010723 or
(ID in
(select wf_FormId
from
dbo.WF_WorkFlow
where
(wf_CurrentOwnerID
Like '%1010723%'
OR
wf_CurrentOwnerID
Like '%1010723%'
OR
wf_HistoricalOwnerID
like '%1010723%' OR
wf_HistoricalOwnerID
like '%1010723%' )
AND wfc_ID =
(select wfc_ID
from
dbo.WF_CfgWorkflow
where fmr_ID = 3
)
)
or
((
select dep_Id
from
PSAData.dbo.Mng_Department
where
dep_DeptNo in
(select emp_DeptNo from PSAData.dbo.Mng_Employee where emp_ID=DATA.EmpID
)
)
in
(0,1013005,1013005,1013615,1013004,1013067,
287,288,1013029,1100663,1100664,1100665,1100695,1100696,1100697,1100712,1100714,
1100727,1100728,1100729,1100718,1100721,1100785,1100670,1100673,1100702,1100674,
1100676,1100705,1100683,1100685,1100691,1100659,1100661,1100662,1100524,1100505,
1100525,1100526,1100527,1100506,1100528,1100529,1100507,1100530,1100509,
1100532,1100533,1100510,1100534,1100535,1100536,1100537,1100569,1100538,
1100571,1100539,1100574,1100540,1100576,1100541,
1100578,1100582,1100583,1100585,1100546,1100586,1100547,1100587,
1100589,1100625,1100627,1100630,1100632,1100597,1100599,1100601,
1100602,1100604,1100606,1100608,1100518,1100520,1100499,1100500,
1100501,1100523,1100503,1100420,1100464,1100452,1100470,1100458,
1100476,1100411,1100427,1100428,1100430,1100436,1100482,1100484,
1100442,1100444,1100415,1100419,1100424,1100429,1100433,1100437,
1100443,1100446,1100449,1100454,1100455,1100457,1100417,1100418,
1100490,1012754,1013660,1013662,1013037,1100776,1013661,1100486,
1013626,1013616,1100488,1100489,1013012,1100407,1013061,1013062,
1013008,1013060,1013005,1013007,1013075,1013673,1013674,1100731,
1013675,1013676,1013671,1013672,1013663,312,1013619,1013620,308,
1013003,1013009,1100590,1100730,1100755,1100756,1100760,1100765,
1100768,1100758,1100759)
) )) ORDER BY NO DESC
EF_SubmitEmpID AS SubmitEmpID, EF_SubmitEmpName AS SubmitEmpName, EF_SubmitDate AS SubmitDate,EF_CreatorEmpID as CreatorEmpID, 5 AS FormType,'转正申请单' AS FormTypeName , EF_DeptNO AS DeptNO, EF_DeptName AS DeptName, EF_EmpDomainName as EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_EmployeeFormal
UNION ALL
SELECT EC_ID AS ID, EC_ApplyBillNo AS NO, EC_EmpID AS EmpID, EC_EmpNo AS EmpNO, EC_EmpName AS EmpName, EC_Status AS Status,
EC_Flag AS Flag, EC_SubmitEmpID AS SubmitEmpID, EC_SubmitEmpName AS SubmitEmpName, EC_SubmitDate AS SubmitDate,EC_CreatorEmpID as CreatorEmpID, case ec_billtypeid
when 0 then 6 when 1 then 11 end AS FormType, '人事调整申请单' AS FormTypeName, EC_DeptNo, EC_DeptName, EC_EmpDomainName,
ecm.ECM_EmpNameList,ecm.ECM_EmpNoList
FROM dbo.EDA_EmployeeChange
left join EDA_EmployeeChangeMulti ecm on EDA_EmployeeChange.EC_ID=ecm.ECM_ChangeID
UNION ALL
SELECT ELM_ID AS ID, ELM_ApplyBillNo AS NO, ELM_EmpID AS EmpID, ELM_EmpNO AS EmpNO, ELM_EmpName AS EmpName, ELM_Status AS Status,
ELM_Flag AS Flag, ELM_SubmitEmpID AS SubmitEmpID, ELM_SubmitEmpName AS SubmitEmpName, ELM_SubmitDate AS SubmitDate, ELM_CreatorEmpID as CreatorEmpID,7 AS FormType, '请假申请单' AS FormTypeName,ELM_DeptNO, ELM_DeptName, ELM_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_EmployeeLeaveMain
UNION ALL
SELECT WAM_ID AS ID, WAM_ApplyBillNo AS NO, WAM_EmpID AS EmpID, WAM_EmpNO AS EmpNO, WAM_EmpName AS EmpName, WAM_Status AS Status,
WAM_Flag AS Flag, WAM_SubmitEmpID AS SubmitEmpID, WAM_SubmitEmpName AS SubmitEmpName, WAM_SubmitDate AS SubmitDate,WAM_CreatorEmpID as CreatorEmpID,8 AS FormType, '福利与补贴申请单' AS FormTypeName, WAM_DeptNO, WAM_DeptName, WAM_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_WelfareAndAllowanceMain
UNION ALL
SELECT DC_ID AS ID, DC_ApplyBillNo AS NO, DC_EmpID AS EmpID, DC_EmpNO AS EmpNO, DC_EmpName AS EmpName, DC_Status AS Status,
DC_Flag AS Flag, DC_SubmitEmpID AS SubmitEmpID, DC_SubmitEmpName AS SubmitEmpName, DC_SubmitDate AS SubmitDate,
DC_CreatorEmpID AS CreatorEmpID,10 AS FormType, '组织结构调整申请单' AS FormTypeName, DC_DeptNO, DC_DeptName, DC_EmpDomainName,
'' as EmpNameList,'' as EmpNoList
FROM dbo.EDA_DepartmentChange)
AS DATA
left Join
(select
wf_FormID,wf_CurrentOwnerName,wf_CurrentOwnerId,wf_CurrentStep,CWF.fmr_ID,wf_VicegerentID
from WF_WorkFlow
AS WF
LEFT JOIN
WF_CfgWorkflow
AS CWF
ON WF.wfc_ID=CWF.wfc_ID
) WFTable
ON DATA.ID = WFTable.wf_formID AND DATA.FORMTYPE=WFTable.fmr_ID
WHERE DATA.FLAG=1
and (
SubmitEmpID=1010723 or
(ID in
(select wf_FormId
from
dbo.WF_WorkFlow
where
(wf_CurrentOwnerID
Like '%1010723%'
OR
wf_CurrentOwnerID
Like '%1010723%'
OR
wf_HistoricalOwnerID
like '%1010723%' OR
wf_HistoricalOwnerID
like '%1010723%' )
AND wfc_ID =
(select wfc_ID
from
dbo.WF_CfgWorkflow
where fmr_ID = 3
)
)
or
((
select dep_Id
from
PSAData.dbo.Mng_Department
where
dep_DeptNo in
(select emp_DeptNo from PSAData.dbo.Mng_Employee where emp_ID=DATA.EmpID
)
)
in
(0,1013005,1013005,1013615,1013004,1013067,
287,288,1013029,1100663,1100664,1100665,1100695,1100696,1100697,1100712,1100714,
1100727,1100728,1100729,1100718,1100721,1100785,1100670,1100673,1100702,1100674,
1100676,1100705,1100683,1100685,1100691,1100659,1100661,1100662,1100524,1100505,
1100525,1100526,1100527,1100506,1100528,1100529,1100507,1100530,1100509,
1100532,1100533,1100510,1100534,1100535,1100536,1100537,1100569,1100538,
1100571,1100539,1100574,1100540,1100576,1100541,
1100578,1100582,1100583,1100585,1100546,1100586,1100547,1100587,
1100589,1100625,1100627,1100630,1100632,1100597,1100599,1100601,
1100602,1100604,1100606,1100608,1100518,1100520,1100499,1100500,
1100501,1100523,1100503,1100420,1100464,1100452,1100470,1100458,
1100476,1100411,1100427,1100428,1100430,1100436,1100482,1100484,
1100442,1100444,1100415,1100419,1100424,1100429,1100433,1100437,
1100443,1100446,1100449,1100454,1100455,1100457,1100417,1100418,
1100490,1012754,1013660,1013662,1013037,1100776,1013661,1100486,
1013626,1013616,1100488,1100489,1013012,1100407,1013061,1013062,
1013008,1013060,1013005,1013007,1013075,1013673,1013674,1100731,
1013675,1013676,1013671,1013672,1013663,312,1013619,1013620,308,
1013003,1013009,1100590,1100730,1100755,1100756,1100760,1100765,
1100768,1100758,1100759)
) )) ORDER BY NO DESC
不要以为写的长的sql就是高手,没有用的,难以调试还效率超低
看一下已经不想再看下去了
2.加索引
2.加注释