需要SQL查询来查找为其员工提供最高薪水的公司名称 [英] Need a SQL query to find out company name who gives highest salary to their employees

问题描述

table employee(#emp_name, city, salary);
table salary(salary,#city);
table company(#Company_name, city);

Find out the Company Name who gives Highest Salary to their Employees.

我有什么试过：



SELECT SUM（e.Salary）FROM employee AS e，薪水AS s，公司AS c WHERE e.city = s.city AND e.city = c.city

What I have tried:

SELECT SUM(e.Salary) FROM employee AS e, salary AS s, company AS c WHERE e.city = s.city AND e.city = c.city

推荐答案

由于这似乎是作业，我不是在写完整的答案......



基本上你很接近。只需将公司名称添加到 SELECT 列表和 GROUP BY 即可。然后使用 ORDER BY 子句对集合进行排序。由于您正在寻找最高金额，因此按降序排列。最后一步，只获取第一行。为此你可以使用 TOP 。



然而，表格设计看起来有点奇怪。员工与公司没有直接关系，但这种关系是通过城市进行的。你还有一个单独的工资表，为什么？

Since this seems to be homework, I'm not writing the complete answer...

Well basically your're close. Just add company name to the SELECT list and GROUP BY it. Then order the set using ORDER BY clause. Since you're looking for the highest sum, order in descending order. And the last step, fetch only the first row. For this you can use TOP.

However, the table design looks a bit odd. Employee does not have direct relation to Company but the relation goes via city. Also you have a separate salary table, why is that?

从公司自然加入薪水中选择company_name，其中薪水=（从工资中选择最高（工资））;

select company_name from company natural join salary where salary = (select max(salary) from salary);

所有表中都是城市公共字段吗？如果是这样，你可以使用公司和工资表的连接查询。



试试这个查询，它会工作



SELECT C.COMPANY_NAME，SUM（S.SALARY）来自COMPANY_NAME C LEFT JOIN SALARY S ON C.CITY = S.CITY GROUP by C.COMPANY_NAME ORDER by DESC



谢谢，

Vijay

Is city common field in all the tables? if so you can use join query for company and salary table.

Try this query, it will work

SELECT C.COMPANY_NAME,SUM(S.SALARY) FROM COMPANY_NAME C LEFT JOIN SALARY S ON C.CITY = S.CITY GROUP BY C.COMPANY_NAME ORDER BY DESC

Thanks,
Vijay

这篇关于需要SQL查询来查找为其员工提供最高薪水的公司名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！