英文:
Find employee who earn highest salary in each department
问题
你当前的解决方案：
SELECT DEPT_NAME,SALARY
FROM EMPLOYEE
WHERE (DEPT_NAME,SALARY) IN (SELECT DEPT_NAME,MAX(SALARY) AS MAXIMUM_SALARY
FROM EMPLOYEE
GROUP BY DEPT_NAME)
我不知道为什么这个查询在PostgreSQL中可以工作，但在SQL Server中不行。它抛出以下错误：
> 在期望条件的上下文中指定了非布尔类型的表达式，附近有 ','。
有人能帮我吗？
这是一个返回多行和多列的子查询。
之后，我使用了连接条件来获得结果，但我想知道为什么上面的查询在SQL Server中不起作用。
英文:
My current solution:
SELECT DEPT_NAME,SALARY
FROM EMPLOYEE
WHERE (DEPT_NAME,SALARY) IN (SELECT DEPT_NAME,MAX(SALARY) AS MAXIMUM_SALARY
FROM EMPLOYEE
GROUP BY DEPT_NAME)
I don't know why this query worked in PostgreSQL but not in SQL-Server. It's throwing the following error:
>An expression of non-boolean type specified in a context where a condition is expected, near ','.
Can anyone help me with this?
This is a subquery which returns multiple rows and columns.
After that I used the join condition which gave me the results but I want to know why the above query using is not working in SQL Server.
答案1
得分: 1
如 @lemon 在评论中指出的，SQL Server 不支持像PostgreSQL和其他关系数据库管理系统那样的元组比较。请查看这个 question 和这个 answer 以获取更多信息。
如果我理解您的目标正确，并且这是为了检索那些在其部门内薪水最高的员工记录，我认为需要一个嵌套查询：
SELECT e.dept_name, e.salary
FROM employee e
JOIN (
SELECT dept_name, MAX(salary) AS maximum_salary
FROM employee
GROUP BY dept_name
) dept_maximum_salary
ON e.dept_name = dept_maximum_salary.dept_name
AND e.salary = dept_maximum_salary.maximum_salary;
英文:
As @lemon pointed out to you in comments, SQL Server doesn't support tuple comparison the way PostgreSQL and other RDBMS do. Check out this question and this answer for more information.
If I am understanding your goal right and this is to retrieve the employees records of those who have the highest salary within their departments, I think a nested query is in order:
SELECT e.dept_name, e.salary
FROM employee e
JOIN (
SELECT dept_name, MAX(salary) AS maximum_salary
FROM employee
GROUP BY dept_name
) dept_maximum_salary
ON e.dept_name = dept_maximum_salary.dept_name
AND e.salary = dept_maximum_salary.maximum_salary;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论