T-SQL 子查询和可能的连接的正确语法 [英] The correct syntax for a T-SQL subquery and a possible join

问题描述

一个子查询的正确语法和连接(如果有的话)是什么，它会从员工的表中返回所有员工的名字和姓氏，并从部门表中返回他们的部门名称，但只有那些员工更多比他们部门的平均工资高?感谢您的回答

What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your answers

推荐答案

这个查询应该给你你正在寻找的东西.

This query should give you what you are looking for.

select firstName, lastName, departmentName 
from Employees e join 
   (select departmentID, departmentName, AVG(salary) AS averageSalary 
     from Department d 
     join Employees e ON e.departmentID=d.departmentID 
     group by departmentId, departmentName) ds
on ds.departmentID=e.departmentID
where e.salary>ds.AverageSalary

(PS:我同意上面的评论.发布你迄今为止尝试过的东西是非常礼貌的.这次你很幸运！:-)

(PS: I agree with the comment above. It's SO etiquette to post what you have tried so far. You were lucky this time! :-)

这篇关于T-SQL 子查询和可能的连接的正确语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！