从下表中退出超过两个雇员的sql查询总薪水明智(尝试过很多人可以帮我这个) [英] Sql query for total salary deptno wise where more than two employess exits from the below table (tried lot can any one help me with this)
本文介绍了从下表中退出超过两个雇员的sql查询总薪水明智(尝试过很多人可以帮我这个)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Empid Deptno Sal
1 1 1000
1 2 2000
2 3 3000
3 4 4000
4 1 5000
2 2 6000
我的尝试:
我使用了一些聚合和排名函数
Empid Deptno Sal
1 1 1000
1 2 2000
2 3 3000
3 4 4000
4 1 5000
2 2 6000
What I have tried:
I used some aggregate and ranking functions
推荐答案
首先,你的数据不好:没有部门该数据中有两名以上的员工,一些员工似乎从两个部门领取薪水,这是......呃......很奇怪。
如果你认为你的意思是超过一个员工到一个部门,那么它可以很容易地完成。
首先按部门将员工分组:这是微不足道的,只是GROUP BY
带有HAVING
子句的语句,将其限制为2个或更多员工:
First off, your data is bad: no department has more than two employees in that data, and some employees appear to be pulling a salary from two departments, which is ... um ... odd.
If you assume that you meant "more than one" employee to a Department, then it can be done very easily.
Start by grouping employees together by department: that's trivial, it's just aGROUP BY
statement with aHAVING
clause to restrict it to 2 or more employees:
GROUP BY DeptNo
HAVING COUNT(EmpId) >= 2
然后您可以将聚合函数应用于组:
Then you can apply your aggregate function to the groups:
SELECT DeptNo, SUM(Sal) AS Total FROM Employees
GROUP BY DeptNo
HAVING COUNT(EmpId) >= 2
这可以为您提供所需的结果:
And that gives you the result you need:
DeptNo Total
1 6000
2 8000
您首先需要获得每个部门的COUNT名员工。获得这些数据后,您可以为每个部门编号创建一个SUM查询。
You first need to get the COUNT of employees for each department. Once you have those figures you can create a SUM query for each department number.
这篇关于从下表中退出超过两个雇员的sql查询总薪水明智(尝试过很多人可以帮我这个)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文