在where子句上带别名的SQL Server内部连接子查询

Question

当我被要求做这件事时，我以为这会很容易，但它一直在我的脑海里。我想使用前一个连接的值应用于后续连接的子查询上的where子句。现在我已经硬编码了一个值，但显然不能保持这样。这是我目前为止写的;

select
SU.SUName
, Q.[Name] AS [QualName]
, DSP.EmployeeID AS [EmployeeID]
, E.FirstName + ' ' + E.LastName AS [FullName]
from SchedulingUnit            AS SU
inner join StaffingProfile      AS SP  on SU.UnitID = SP.UnitID
inner join DailyStaffingProfile AS DSP on SP.ID = DSP.ID
inner join (SELECT QualID, [Name] FROM Qualifications WHERE QualID = **56**
UNION
SELECT BaseQualificationID, [Name]
from QualificationDependencies AS QD
inner join Qualifications AS Q ON Q.QualID = QD.QualifiesForID
WHERE QualifiesForID IN (SELECT QualID FROM Qualifications WHERE QualID = **56** )) 
AS Q ON SP.QualID = Q.QualID
inner join Employee             AS E   on DSP.EmployeeID = E.EmpID
where (@UnitID IS NULL OR SU.UnitID = @UnitID)
and SU.HideInList = 0
and NOT EXISTS (select * from EQ where EmpID = E.EmpID and SP.QualID = EQ.QualID)

我想用SP.QualID…代替56的2个实例。这可能吗?

Answer 1

您不需要将SP.QualID放在子查询中。子查询之后的SP.QualID = Q.QualID已经做了您想做的事情。只需删除子查询中的两个WHERE QualID = 56。