这个 sql如何优化

Question

                 LEFT JOIN (
                                                    SELECT 
                                                        Month                   = NULL,  
                                                    Employee_ID = isnull(project.Employee_ID,leavenonproject.Employee_ID),
                                                    WorkContent_ID = isnull(project.WorkContent_ID,''),
                                                    ProjectEffortHours = isnull(project.EffortHours,0),
                                                    BillableApprovedHours = isnull(project.BillableApprovedHours,0),
                                                        UnBillableApprovedHours = isnull(project.UnBillableApprovedHours,0),
                                                    LeaveEnteredHours = isnull(leavenonproject.LeaveEntered,0),
                                                    LeaveApprovedHour = isnull(leavenonproject.LeaveApproved,0),
                                                    NonProjectEnteredHours  = isnull(leavenonproject.NonProjectEntered,0),
                                                    NonProjectApprovedHours = isnull(leavenonproject.NonProjectApproved,0)
                                                    FROM
                                                            (SELECT 
                                                    Month = NULL,
                                                    Employee_ID     = TimeSheetList.Employee_ID,
                                                                WorkType_ID     = TimeSheetList.WorkType_ID,
                                                                WorkTypeName    = NULL,
                                                    WorkContent_ID  = WorkContent_ID,
                                                            EffortHours     = SUM(EffortHours),
                                                    BillableApprovedHours= SUM(BillableApprovedHours) ,
                                                    UnBillableApprovedHours = SUM(UnBillableApprovedHours)
                                                    FROM
                                                    TimeSheetList
                                                                INNER JOIN Employee AS employee ON TimeSheetList.Employee_ID = employee.ID
                                                                INNER JOIN InternalTransfer as transfer ON (employee.ID = transfer.Employee_ID {0})
                                                                INNER JOIN SubCircle AS subcircle ON (subcircle.ID = transfer.SubCircle_ID {2})
                                                                INNER JOIN Circle AS circle ON (circle.ID = subcircle.Circle_ID {1})
                                                                INNER JOIN Project AS project ON (project.ID = TimeSheetList.WorkContent_ID)
                                                    WHERE
                                                    TimeSheetList.Status = 1 AND WorkType_ID = 1 {3}
                                                    GROUP BY TimeSheetList.Employee_ID, TimeSheetList.WorkType_ID, WorkContent_ID, transfer.StartDate, transfer.EndDate, circle.Name, subcircle.Name
                                                            ) AS project

Answer 1

 FULL JOIN 
                                                            (SELECT 
                                                Month              = NULL,
                                                Employee_ID        = isnull(leave.Employee_ID,nonproject.Employee_ID),
                                                LeaveEntered       = isnull(leave.EffortHours,0),
                                                NonProjectEntered  = isnull(nonproject.EffortHours,0),
                                                LeaveApproved      = isnull(leave.ApprovedHours,0),
                                                NonProjectApproved = isnull(nonproject.ApprovedHours,0),
                                                WorkContent_ID     = NULL 
                                                FROM
                                                                (SELECT 
                                                        Month = NULL,
                                                        Employee_ID     = TimeSheetList.Employee_ID,
                                                                    WorkType_ID     = TimeSheetList.WorkType_ID,
                                                                    WorkTypeName    = NULL,
                                                        WorkContent_ID  = NULL,
                                                                EffortHours     = SUM(EffortHours),
                                                        ApprovedHours = SUM(ApprovedHours)
                                                        FROM
                                                        TimeSheetList
                                                                    INNER JOIN Employee AS employee ON TimeSheetList.Employee_ID = employee.ID
                                                                    INNER JOIN InternalTransfer as transfer ON (employee.ID = transfer.Employee_ID {0})
                                                                    INNER JOIN SubCircle AS subcircle ON (subcircle.ID = transfer.SubCircle_ID {2})
                                                                    INNER JOIN Circle AS circle ON (circle.ID = subcircle.Circle_ID {1})
                                                        WHERE
                                                        TimeSheetList.Status = 1 AND WorkType_ID = 2 {3}
                                                        GROUP BY TimeSheetList.Employee_ID, TimeSheetList.WorkType_ID, transfer.StartDate, transfer.EndDate, circle.Name, subcircle.Name
                                                                ) AS leave
                                                            FULL JOIN
                                                                (SELECT 
                                                        Month = NULL,
                                                        Employee_ID     = TimeSheetList.Employee_ID,
                                                                    WorkType_ID     = TimeSheetList.WorkType_ID,
                                                                    WorkTypeName    = activity.Name,
                                                        WorkContent_ID  = NULL,
                                                                EffortHours     = SUM(EffortHours),
                                                        ApprovedHours = SUM(ApprovedHours)
                                                        FROM
                                                        TimeSheetList
                                                                    INNER JOIN Employee AS employee ON TimeSheetList.Employee_ID = employee.ID
                                                                    INNER JOIN InternalTransfer as transfer ON (employee.ID = transfer.Employee_ID {0})
                                                                    INNER JOIN SubCircle AS subcircle ON (subcircle.ID = transfer.SubCircle_ID {2})
                                                                    INNER JOIN Circle AS circle ON (circle.ID = subcircle.Circle_ID {1})
                                                                    INNER JOIN Activity AS activity ON activity.ID = TimeSheetList.WorkType_ID
                                                        WHERE
                                                        TimeSheetList.Status = 1 AND TimeSheetList.WorkType_ID <> 1 AND TimeSheetList.WorkType_ID <> 2 {3}
                                                        GROUP BY TimeSheetList.Employee_ID, TimeSheetList.WorkType_ID, activity.Name, transfer.StartDate, transfer.EndDate, circle.Name, subcircle.Name
                                                                ) AS nonproject ON leave.Employee_ID=nonproject.Employee_ID
                                                        ) AS leavenonproject ON project.Employee_ID=leavenonproject.Employee_ID
                                                ) AS timesheet ON employee.ID = timesheet.Employee_ID

Answer 2

 LEFT JOIN 
                                            (
                                            SELECT DISTINCT
                                            ID = project.ID,                                             
                                            Billable = resource.Billable,
                                            CustomerName= customer.Name,
                                            ProjectID = project.ProjectID,
                                            ProjectName = project.ProjectName,
                                            ProjectType = projecttype.Name,
                                                        TaskRole    = taskrole.Name,
                                                        Employee_ID  = resource.Employee_ID
                                            FROM
                                        Project AS project                                             
                                            INNER JOIN Customer AS customer ON (customer.ID = project.Customer_ID)
                                            INNER JOIN ProjectType AS projecttype ON (projecttype.ID = project.ProjectType_ID)
                                                        INNER JOIN ResourcePlan AS resource ON (resource.Project_ID = project.ID)
                                                        INNER JOIN TaskRole AS taskrole ON (taskrole.ID = resource.TaskRole_ID)    
                                            ) AS project ON (timesheet.Employee_ID = project.Employee_ID AND timesheet.WorkContent_ID = project.ID {4})
                                            LEFT JOIN
                                                (
                                                    SELECT 
                                            Employee_ID     = TimeSheetList.Employee_ID,
                                                        AllProjectHours = SUM(EffortHours),
                                                        AllApprovedProjectHours = SUM(ApprovedHours)
                                            FROM
                                            TimeSheetList
                                                        INNER JOIN Employee AS employee ON TimeSheetList.Employee_ID = employee.ID
                                                        INNER JOIN InternalTransfer as transfer ON (employee.ID = transfer.Employee_ID {0})
                                                        INNER JOIN SubCircle AS subcircle ON (subcircle.ID = transfer.SubCircle_ID {2})
                                                        INNER JOIN Circle AS circle ON (circle.ID = subcircle.Circle_ID {1})
                                                        INNER JOIN Project AS project ON (project.ID = TimeSheetList.WorkContent_ID)
                                            WHERE
                                            TimeSheetList.Status = 1 AND WorkType_ID = 1 {3}
                                            GROUP BY TimeSheetList.Employee_ID
                                                ) AS allproject ON allproject.Employee_ID = employee.ID
                                             ORDER BY AssociateID ASC";
                string condition1 = " AND transfer.StartDate <= '" + endDate.ToShortDateString() + "' AND (transfer.EndDate IS NULL OR transfer.EndDate >= '" + startDate.ToShortDateString() + "')";
                string condition2 = "";
                string condition3 = "";