Server需要查询高于平均薪资和低于每个办公室经理薪资的薪资

Question

我有两张这样的桌子。

CREATE TABLE Branch
(
    branchID int,
    b_location varchar(50),
    b_contactNo int,

    CONSTRAINT PK_Branch PRIMARY KEY (branchID)
);   

CREATE TABLE Staff
(
    staffID int,
    fullName varchar(100) NOT NULL,
    s_category varchar(25),
    s_email varchar(50),
    s_contactNo int,
    speciality varchar(100),
    qualifications varchar(250),
    pre_employment varchar(200),
    salary numeric(8,2),
    staff_gender char(1),
    staff_joined_date datetime,
    branch_allocated int,

    CONSTRAINT PK_Staff PRIMARY KEY (staffID),

    CONSTRAINT FK_Staff_Branch 
        FOREIGN KEY (branch_allocated) REFERENCES Branch(branchID) ON DELETE CASCADE,

    CONSTRAINT CHK_StaffGender CHECK (staff_gender='M' OR staff_gender='F'),
    CONSTRAINT CHK_FullName CHECK (fullName NOT LIKE '%[^A-Za-z ]%'),
    CONSTRAINT CHK_SALARY CHECK (salary>0 AND salary<=150000)
);

Staff表有以下示例数据：

 7 | Nick Carter   | Nurse          | nick@mail.com   | 32145657 | N/A | N/A | 5 Years in London General |  99000.00 | M | 2013-01-04 10:00:00.000 | 1
 8 | Brian OConner | Nurse          | brian@mail.com  | 32678579 | N/A | N/A | 3 Years in London General |  83000.00 | M | 2015-01-09 10:00:00.000 | 2
12 | Jayden Smith  | Office Manager | jayden@mail.com |  5678755 | N/A | N/A | N/A                       | 145000.00 | M | 2013-01-04 10:00:00.000 | 1
13 | Will Smith    | Office Manager | will@mail.com   | 12345678 | N/A | N/A | N/A                       | 135000.00 | M | 2013-01-04 10:00:00.000 | 2

我想知道每个分公司员工的平均工资高于平均水平，而每个分公司的办公室经理的工资低于平均水平。

我写了一些问题。但不知道该怎么做。

SELECT 
    s.staffId AS ID, s.fullName AS Name, s.s_category AS Category, s.salary AS Salary, b.branchId AS 'Branch No'
FROM 
    Staff s 
INNER JOIN 
    Branch b ON s.branch_allocated = b.branchID
WHERE 
    salary > (SELECT AVG(salary) FROM Staff)

WITH tempTable AS 
(
    SELECT DISTINCT branch_allocated, AVG(salary) AS AVG_SAL 
    FROM Staff
    GROUP BY branch_allocated
)
SELECT AVG_SAL 
FROM tempTable

Answer 1

将WHERE查询更改为

WHERE 
    salary > ( SELECT AVG(salary) 
               FROM   Staff x 
               WHERE  x.branch_allocated = s.branch_allocated )
AND
    Salary < ( SELECT MIN(salary) 
               FROM   Staff x 
               WHERE  x.s_category = 'Office Manager' ) 

Answer 2

SELECT s.staffId AS ID, 
       s.fullName AS Name, 
       s.s_category AS Category, 
       s.salary AS Salary, 
       b.branchId AS 'Branch No'
FROM Staff s 
INNER JOIN Branch b 
ON s.branch_allocated = b.branchID
WHERE salary > ( SELECT AVG(salary) 
           FROM   Staff x 
           WHERE  x.branch_allocated = s.branch_allocated )
AND salary < (SELECT MIN(salary) FROM Staff  
          where speciality ='Office Manager' Group by branch_allocated)

Answer 3

with empavgsal as(
SELECT s.staffId AS ID, s.fullName AS Name, s.s_category AS Category, s.salary AS Salary, b.branchId AS 'BranchNo' FROM Staff s INNER JOIN Branch b ON s.branch_allocated = b.branchID WHERE s.salary > (SELECT AVG(salary) from Staff Group By s_category where s_category!='Office Manager') 
)
Select e.ID,e.Name,e. Category,e.Salary,e.BranchNo FROM empavgsal INNER JOIN Staff s ON
s.staffId!=e.ID WHERE e.salary<s.s salary AND s.s_category ='Office Manager'