从下表中退出超过两个雇员的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)

问题描述

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 a GROUP BY statement with a HAVING 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屋！