编写一个SQL代码来选择所有工资高于部门平均工资且超过3万美元的员工

Question

薪资(department_id，employee_id，salary(

DepartmentID EmployeeID      Salary
1          1            $30,000.00
1          2            $25,000.00
1          3            $40,000.00
1          4            $33,000.00
2          5            $25,000.00
2          6            $50,000.00

Answer 1

这可能适用于

select s1.employeeid, s1.departmentid, s1.salary
from salary as s1
inner join
(select departmentid, avg(salary) as avgsalary
from salary 
group by departmentid) as s2   
on s1.departmentid = s2.departmentid
where salary > avgsalary and salary > 30000
;

Answer 2

尝试此查询：

;WITH CTE AS
(
SELECT DepartmentID, AVG(salary) AverageSalary FROM Salary
GROUP BY DepartmentID
)
SELECT s.EmployeeID, s.DepartmentID, s.Salary, c.AverageSalary  
FROM Salary s
INNER JOIN CTE c on c.DepartmentID = s.DepartmentID
WHERE s.salary > c.AverageSalary and s.salary > 30000

Answer 3

您可以使用以下查询

SELECT t1.DepartmentID,t2.EmployeeID,t2.Salary,t1.average 
FROM
(SELECT DepartmentID,AVG(Cast(Salary as Float)) as average
FROM Test
GROUP BY DepartmentID) t1 
JOIN Test t2 ON t1.DepartmentID = t2.DepartmentID AND t2.Salary > t1.average AND t2.Salary > 30

结果：dbfiddle