sql-server - 避免在 WHERE 子句中引用表两次

Question

以下是我在 SQL Server 2005 中的数据库的简化版本。我需要根据业务部门选择员工。每个员工都有家庭部门、上级部门和访问部门。以部门为单位，可以查出事业单位。

• 对于员工，如果 HomeDeptID = ParentDeptID，则@SearchBusinessUnitCD 应该存在于 VisitingDeptID。
• 如果 HomeDeptID <> ParentDeptID，那么@SearchBusinessUnitCD 应该是代表 ParentDeptID。

以下查询工作正常。但它对 #DepartmentBusinesses 表进行了两次扫描。有没有办法通过将表 #DepartmentBusinesses 设为 CASE 语句或类似语句来仅使用一次？

DECLARE @SearchBusinessUnitCD CHAR(3)
SET @SearchBusinessUnitCD = 'B'

--IF HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID
--IF HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID

CREATE TABLE #DepartmentBusinesses (DeptID INT, BusinessUnitCD CHAR(3))
INSERT INTO #DepartmentBusinesses
    SELECT 1, 'A' UNION ALL 
    SELECT 2, 'B' 

CREATE NONCLUSTERED INDEX IX_DepartmentBusinesses_DeptIDBusinessUnitCD ON #DepartmentBusinesses (DeptID,BusinessUnitCD)

DECLARE @Employees TABLE (EmpID INT, HomeDeptID INT, ParentDeptID INT, VisitingDeptID INT)
INSERT INTO @Employees 
    SELECT 1, 1, 1, 2 UNION ALL
    SELECT 2, 2, 1, 3

SELECT * 
FROM @Employees
WHERE
    (
            HomeDeptID = ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = VisitingDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD)
            )
    OR 
    (
            HomeDeptID <> ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = ParentDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD
            )
    )

DROP TABLE #DepartmentBusinesses

计划

Answer 1

SELECT * 
FROM @Employees e
WHERE EXISTS (
        SELECT 1
        FROM #DepartmentBusinesses t
        WHERE t.BusinessUnitCD = @SearchBusinessUnitCD
            AND (
                (e.HomeDeptID = e.ParentDeptID AND t.DeptID = e.VisitingDeptID)
                OR
                (e.HomeDeptID != e.ParentDeptID AND t.DeptID = e.ParentDeptID)
            )
    )