多部分的内连接(选择)问题中的交叉连接数据

Question

我需要使用"交叉连接"来组合 2 个表，问题是当与第三个表进行"内部连接"时，它不再识别cd.WorkDate

我遇到的错误是

找不到多部分标识符"cd.WorkDate">

UPDATE
Emps
SET
CanWork1 = CASE WHEN cd.WeekDay = 1 THEN 0 ELSE CanWork1 END,
CanWork2 = CASE WHEN cd.WeekDay = 2 THEN 0 ELSE CanWork2 END,
CanWork3 = CASE WHEN cd.WeekDay = 3 THEN 0 ELSE CanWork3 END,
CanWork4 = CASE WHEN cd.WeekDay = 4 THEN 0 ELSE CanWork4 END,
CanWork5 = CASE WHEN cd.WeekDay = 5 THEN 0 ELSE CanWork5 END,
CanWork6 = CASE WHEN cd.WeekDay = 6 THEN 0 ELSE CanWork6 END,
CanWork7 = CASE WHEN cd.WeekDay = 7 THEN 0 ELSE CanWork7 END
FROM
#PS_EMPLOYEES AS Emps
CROSS JOIN (SELECT WorkDate, WeekDay
FROM #checkDays) AS cd
LEFT JOIN
(SELECT EmployeeNumber, NewStatus
FROM dbo.tblPAY_Employees_StatusHistory
WHERE cd.WorkDate BETWEEN StartDate AND EndDate) AS Stat
ON Emps.EmployeeNumber = Stat.EmployeeNumber
WHERE
IsNull(Stat.NewStatus,2) <> 1

Answer 1

您收到该错误的原因是因为您正在子查询中访问cd.WorkDate，如果您将其 where 条件从中取出，查询应该很好。此外，您的查询应该在没有子查询的情况下工作left join.请尝试以下操作。

UPDATE
Emps
SET
CanWork1 = CASE WHEN cd.WeekDay = 1 THEN 0 ELSE CanWork1 END,
CanWork2 = CASE WHEN cd.WeekDay = 2 THEN 0 ELSE CanWork2 END,
CanWork3 = CASE WHEN cd.WeekDay = 3 THEN 0 ELSE CanWork3 END,
CanWork4 = CASE WHEN cd.WeekDay = 4 THEN 0 ELSE CanWork4 END,
CanWork5 = CASE WHEN cd.WeekDay = 5 THEN 0 ELSE CanWork5 END,
CanWork6 = CASE WHEN cd.WeekDay = 6 THEN 0 ELSE CanWork6 END,
CanWork7 = CASE WHEN cd.WeekDay = 7 THEN 0 ELSE CanWork7 END
FROM
#PS_EMPLOYEES AS Emps
CROSS JOIN 
(
SELECT 
WorkDate, 
WeekDay
FROM #checkDays
) AS cd
LEFT JOIN dbo.tblPAY_Employees_StatusHistory stat
ON Emps.EmployeeNumber = Stat.EmployeeNumber
WHERE cd.WorkDate BETWEEN stat.StartDate AND stat.EndDate
AND IsNull(Stat.NewStatus,2) <> 1

Answer 2

我不清楚为什么你在from子句中使用任何子查询。 这更简单且等效：

FROM #PS_EMPLOYEES Emps CROSS JOIN
#checkDays cd LEFT JOIN
dbo.tblPAY_Employees_StatusHistory stat
ON cd.WorkDate BETWEEN stat.StartDate AND stat.EndDate AND
Emps.EmployeeNumber = stat.EmployeeNumber

在SQL Server(和大多数数据库(中，只选择几列的子查询是多余的。 优化器忽略它们 - 实际上优化器只处理表中所需的列。

但是，正如我在评论中指出的那样，这可能并没有达到您真正想要的效果。 特别是，如果FROM子句导致emps中的任何行都有多行，则更新将更新为单个任意行。