SQL Server包含变量的双大小写语句效率低下

Question

这可能只是一种不知道搜索什么的情况，所以这个方向的答案是完全可以接受的。我正在将数据推送到我的SQL服务器，并进行了以下操作(模拟(，这很有效，但似乎效率很低。我最终更愿意消除我所有的变量。

--From PHP
declare @employeeid varchar(25) = '100971'
declare @reviewscore as tinyint = 52
declare @payclass as varchar(25) = 'salary management'
--/From PHP
declare @wagequartile as tinyint = (select quartile from activeemployees where EmployeeID= @employeeid)
declare @scorequartile as tinyint = (
select
case
when @reviewscore <= max1 then 1
when @reviewscore <= max2 then 2
when @reviewscore <= max3 then 3
else 4
end as ScoreQuartile
from ReviewScoreMatrix where PayClass=@payclass
)
select
case
when @scorequartile = 1 then ScoreQuartile1
when @scorequartile = 2 then ScoreQuartile2
when @scorequartile = 3 then ScoreQuartile3
else 4
end as PercentRaise
from RaisePercentMatrix
where WageQuartile = @wagequartile

我试着用CTE打球，但不太明白这是如何让它变得更好的。相关章节：

with cte1 as (
select
case
when @reviewscore <= max1 then 1
when @reviewscore <= max2 then 2
when @reviewscore <= max3 then 3
else 4
end as [ScoreQuartile]
from ReviewScoreMatrix where PayClass=@payclass
)
select
case
when (select top 1 ScoreQuartile from cte1) = 1 then ScoreQuartile1
when (select top 1 ScoreQuartile from cte1) = 2 then ScoreQuartile2
when (select top 1 ScoreQuartile from cte1) = 3 then ScoreQuartile3
else 4
end as PercentRaise
from RaisePercentMatrix
where WageQuartile = @wagequartile

我觉得我在CTE方面走在了正确的轨道上，但嵌入cte1中的select似乎是一个失误。感谢您的帮助。

Answer 1

如果我传输正确，那么你的代码可以变成这样：

declare 
@employeeid varchar(25) = '100971',
@reviewscore as tinyint = 52,
@payclass as varchar(25) = 'salary management';
select      PercentRaise = 
case 
when @reviewScore <= rsm.max1 then rpm.ScoreQuartile1
when @reviewScore <= rsm.max2 then rpm.ScoreQuartile2
when @reviewScore <= rsm.max3 then rpm.ScoreQuartile3
else 4
end
from        activeEmployees e
join        raisePercentMatrix rpm on e.quartile = rpm.wageQuartile
left join   reviewScoreMatrix rsm on rsm.payClass = @payclass
where       e.EmployeeID = @employeeid;

但是，如果你在某个地方有一个评估分数表和每个员工的工资等级分配表，那么你可以查询所有员工：

select      e.employeeId,
PercentRaise = 
case 
when rs.reviewScore <= rsm.max1 then rpm.ScoreQuartile1
when rs.reviewScore <= rsm.max2 then rpm.ScoreQuartile2
when rs.reviewScore <= rsm.max3 then rpm.ScoreQuartile3
else 4
end
from        activeEmployees e
join        reviewScores rs on e.employeeId = rs.employeeId
join        payClassInfo pci on e.employeeId = pci.employeeId
join        raisePercentMatrix rpm on e.quartile = rpm.wageQuartile
left join   reviewScoreMatrix rsm on rsm.payClass = pci.payclass;