时间戳

首页 > 解决方案 > 具有变量低效率的 SQL Server 双案例语句

sql - 具有变量低效率的 SQL Server 双案例语句

问题描述

这可能只是不知道要搜索什么的情况，因此具有该方向的答案是完全可以接受的。我正在将数据推送到我的 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 嵌入选择似乎在那里错过了。任何帮助表示赞赏。

标签： sqlsql-servercase

解决方案

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

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;

推荐阅读