SQL Server查询需求：薪资高于平均且低于各分行经理薪资

Got it，我来帮你搞定这个SQL Server查询需求。先把需求拆成两个核心条件，咱们一步步来构建查询：

• 员工薪资高于全体员工的平均薪资
• 员工薪资低于其所在分行经理的薪资

首先得明确一个前提：我假设Staff表的s_category字段是用来区分员工类型的，比如经理的类别值是'Manager'。如果你的系统里用其他方式标识经理（比如专门的is_manager字段），后面只需要对应调整筛选条件就行。

完整SQL查询语句 #

WITH AvgSalary AS (
    -- 先算出全体员工的平均薪资
    SELECT AVG(salary) AS overall_avg_salary
    FROM Staff
),
BranchManagers AS (
    -- 获取每个分行对应的经理薪资（这里假设s_category为'Manager'就是经理）
    SELECT 
        branch_allocated AS branchID,
        salary AS manager_salary
    FROM Staff
    WHERE s_category = 'Manager'
)
SELECT 
    s.*
FROM Staff s
-- 把全体平均薪资关联到每条员工记录上
CROSS JOIN AvgSalary avg_sal
-- 关联到员工所在分行的经理薪资
JOIN BranchManagers bm ON s.branch_allocated = bm.branchID
WHERE 
    -- 满足第一个条件：薪资高于全体平均
    s.salary > avg_sal.overall_avg_salary
    -- 满足第二个条件：薪资低于所在分行经理
    AND s.salary < bm.manager_salary
    -- 排除经理本身，避免自己和自己比较的无效数据
    AND s.s_category != 'Manager';

代码细节解释 #

1. AvgSalary CTE：用AVG()函数计算所有员工的薪资平均值，作为后续筛选的基准值。
2. BranchManagers CTE：筛选出所有经理的记录，通过branch_allocated关联到对应的分行，这样每个分行都能拿到经理的薪资。
3. 主查询部分：
   • 用CROSS JOIN把全体平均薪资和员工表关联，确保每条员工记录都能获取到这个平均薪资值。
   • 通过branch_allocated字段把员工和其所在分行的经理薪资关联起来。
   • 最后用WHERE子句同时满足两个薪资条件，并且排除经理自己，避免出现不符合逻辑的结果。

额外说明 #

• 如果你的系统里一个分行有多个经理，那可能需要调整BranchManagers的逻辑，比如取经理的最高薪资（用MAX(salary)）或者最低薪资（MIN(salary)），具体取决于业务需求。
• 如果区分经理的字段不是s_category，比如用staff_role = 'Manager'，直接修改BranchManagers里的WHERE条件就行。

内容的提问来源于stack exchange，提问作者u_u-de