提高包含子查询的视图的性能

本文介绍了提高包含子查询的视图的性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

SELECT dbo.EmployeeMaster.Employee_Id, dbo.EmployeeMaster.Emp_Id, dbo.EmployeeMaster.Emp_Name, FIRSTIN.Shift_Id, FIRSTIN.EmpPresent_EntryDate, FIRSTIN.EmpPresent_InTime AS FirstInTime, LASTOUT.EmpPresent_OutTime AS LastOutTime, FIRSTIN.EmpPresent_EmpInTime AS Emp_FirstInTime, LASTOUT.EmpPresent_EmpOutTime AS Emp_LastOutTime, FIRSTIN.Status , FIRSTIN.EmpPresent_Permission, FIRSTIN.EmpPresent_Remarks AS FirstRemark, FIRSTIN.EmpPresent_permission_by, FIRSTIN.Remark_for_permission, dbo.EmployeeMaster.Emp_Category_Id, FIRSTIN.EmpPresent_Id, dbo.EmployeeMaster.Emp_ConId, dbo.EmployeeMaster.Emp_ConName, dbo.EmployeeMaster.OTAllow FROM dbo.EmployeeMaster INNER JOIN dbo.EmpPresentDetail AS FIRSTIN ON dbo.EmployeeMaster.Employee_Id = FIRSTIN.Employee_Id INNER JOIN dbo.EmpPresentDetail AS LASTOUT ON dbo.EmployeeMaster.Employee_Id = LASTOUT.Employee_Id AND FIRSTIN.EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate WHERE (FIRSTIN.EmpPresent_Id = (SELECT MIN(EmpPresent_Id) AS Expr1 FROM dbo.EmpPresentDetail WHERE (Employee_Id = FIRSTIN.Employee_Id) AND (EmpPresent_EntryDate = FIRSTIN.EmpPresent_EntryDate) ) ) AND (LASTOUT.EmpPresent_Id = (SELECT MAX(EmpPresent_Id) AS Expr1 FROM dbo.EmpPresentDetail AS EmpPresentDetail_2 WHERE (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate) AND (EmpPresent_EmpOutTime IS NULL OR EmpPresent_EmpOutTime = (SELECT MAX(EmpPresent_EmpOutTime) AS Expr1 FROM dbo.EmpPresentDetail AS EmpPresentDetail_1 WHERE (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate)))))

我想仅使用不包含subquery的内部联接进行此查询joinnings ... 让我知道是否需要进一步的帮助. 问候

SELECT dbo.EmployeeMaster.Employee_Id, dbo.EmployeeMaster.Emp_Id, dbo.EmployeeMaster.Emp_Name, FIRSTIN.Shift_Id, FIRSTIN.EmpPresent_EntryDate, FIRSTIN.EmpPresent_InTime AS FirstInTime, LASTOUT.EmpPresent_OutTime AS LastOutTime, FIRSTIN.EmpPresent_EmpInTime AS Emp_FirstInTime, LASTOUT.EmpPresent_EmpOutTime AS Emp_LastOutTime, FIRSTIN.Status , FIRSTIN.EmpPresent_Permission, FIRSTIN.EmpPresent_Remarks AS FirstRemark, FIRSTIN.EmpPresent_permission_by, FIRSTIN.Remark_for_permission, dbo.EmployeeMaster.Emp_Category_Id, FIRSTIN.EmpPresent_Id, dbo.EmployeeMaster.Emp_ConId, dbo.EmployeeMaster.Emp_ConName, dbo.EmployeeMaster.OTAllow FROM dbo.EmployeeMaster INNER JOIN dbo.EmpPresentDetail AS FIRSTIN ON dbo.EmployeeMaster.Employee_Id = FIRSTIN.Employee_Id INNER JOIN dbo.EmpPresentDetail AS LASTOUT ON dbo.EmployeeMaster.Employee_Id = LASTOUT.Employee_Id AND FIRSTIN.EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate WHERE (FIRSTIN.EmpPresent_Id = (SELECT MIN(EmpPresent_Id) AS Expr1 FROM dbo.EmpPresentDetail WHERE (Employee_Id = FIRSTIN.Employee_Id) AND (EmpPresent_EntryDate = FIRSTIN.EmpPresent_EntryDate) ) ) AND (LASTOUT.EmpPresent_Id = (SELECT MAX(EmpPresent_Id) AS Expr1 FROM dbo.EmpPresentDetail AS EmpPresentDetail_2 WHERE (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate) AND (EmpPresent_EmpOutTime IS NULL OR EmpPresent_EmpOutTime = (SELECT MAX(EmpPresent_EmpOutTime) AS Expr1 FROM dbo.EmpPresentDetail AS EmpPresentDetail_1 WHERE (Employee_Id = LASTOUT.Employee_Id) AND (EmpPresent_EntryDate = LASTOUT.EmpPresent_EntryDate)))))

I Want to This Query using Only Inner join which not contains subquery

解决方案

Create CTEs which calculate the max/min grouped on the columns in where clause and use those CTEs with joinnings... Let me know if further assistance is required. Regards