将SQL标量函数结果与SQL中的列进行比较

Question

我有这个查询

select 
x1.name, x1.owner,
(Select dbo.GetTopEmployerName(x1.unit)),
X2.searchName, x4.code,x6.status
From 
dbo.profile X1
Join 
dbo.profilevalues x3 on X1.profileId == x3.profileKey
Left outer join 
dbo.profilecontact X2 on x1.unit = X2.ctId
Join 
dbo.accessright x4 on x3.accessrightsId == x4.accessrightId
Left outer join 
dbo.profilecontact x5 on x1.owner = x5.contactId
Left outer join 
dbo.status x6 on x1.statusId = x6.statusId
Where
((X4.accessRightid in (200,300,400);
))

这里我需要比较函数dbo.GetTopEmployerName()和x4.code的结果。

我需要检查一下它们是否不一样。

Answer 1

将查询封装在派生表中:

select name, owner, TopEmployerName, searchName, code, status
from
(
select 
x1.name, x1.owner,
(Select dbo.GetTopEmployerName(x1.unit)) TopEmployerName,
X2.searchName, x4.code, x6.status
From 
dbo.profile X1
Join 
dbo.profilevalues x3 on X1.profileId == x3.profileKey
Left outer join 
dbo.profilecontact X2 on x1.unit = X2.ctId
Join 
dbo.accessright x4 on x3.accessrightsId == x4.accessrightId
Left outer join 
dbo.profilecontact x5 on x1.owner = x5.contactId
Left outer join 
dbo.status x6 on x1.statusId = x6.statusId
Where
X4.accessRightid in (200,300,400)
) dt
where TopEmployerName = code