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

LCJ 发表于 Dev

LCJ

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

对于员工，如果HomeDeptID = ParentDeptID，则VisitingDeptID应该存在@SearchBusinessUnitCD。
如果HomeDeptID <> ParentDeptID，则对于ParentDeptID，@ SearchBusinessUnitCD应该存在。

以下查询工作正常。但是它已经在#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

计划

德瓦特

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)
            )
    )

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。