SQL 选择员工人数超过 2 人且薪水大于 1000 的部门名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4646149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select dept names who have more than 2 employees whose salary is greater than 1000
提问by Anshul
How would do the following in SQL
如何在 SQL 中执行以下操作
"select dept names who have more than 2 employees whose salary is greater than 1000" ?
“选择有超过 2 名员工的工资大于 1000 的部门名称”？
DeptId DeptName
------ --------
1 one
2 two
3 three
EmpId DeptId Salary
----- ------ ------
121 1 2000
122 1 2000
123 1 5000
124 1 4000
131 2 2000
132 2 6000
133 2 1000
134 2 1000
125 3 1000
126 3 20000
RESULT: one
回答by John Petrak
How about something like this?
这样的事情怎么样？
SELECT D.DeptName FROM
Department D WHERE (SELECT COUNT(*)
FROM Employee E
WHERE E.DeptID = D.DeptID AND
E.Salary > 1000) > 2
回答by D23
SELECT DEPTNAME
FROM(SELECT D.DEPTNAME,COUNT(EMPID) AS TOTEMP
FROM DEPT AS D,EMPLOYEE AS E
WHERE D.DEPTID=E.DEPTID AND SALARY>1000
GROUP BY D.DEPTID
)
WHERE TOTEMP>2;
回答by Pankaj Agarwal
select min(DEPARTMENT.DeptName) as deptname
from DEPARTMENT
inner join employee on
DEPARTMENT.DeptId = employee.DeptId
where Salary > 1000
group by (EmpId) having count(EmpId) > =2
回答by user763822
hope this helps
希望这可以帮助
select DeptName from DEPARTMENT inner join EMPLOYEE using (DeptId) where Salary>1000 group by DeptName having count(*)>2
回答by Deepak Varshney
select deptname from dept_1
where exists
(
SELECT DeptId,COUNT(*)
FROM emp_1
where salary>1000
and emp_1.deptid=dept_1.deptid
GROUP BY DeptId
having count(*)>2)
回答by Pankaj Singh
select D.DeptName from [Department] D where D.DeptID in
(
select E.DeptId from [Employee] E
where E.Salary > 1000
group by E.DeptId
having count(*) > 2
)
回答by UMAIR SHAH
1:list name of all employee who earn more than RS.100000 in a year.
1：列出年收入超过RS.100000的所有员工的姓名。
2:give the name of employee who earn heads the department where employee with employee I.D
2：提供员工姓名，员工姓名为员工所在部门的负责人
回答by onedaywhen
My main advice would be to steer clear of the HAVINGclause (see below):
我的主要建议是避开该HAVING条款（见下文）：
WITH HighEarners AS
( SELECT EmpId, DeptId
FROM EMPLOYEE
WHERE Salary > 1000 ),
DeptmentHighEarnerTallies AS
( SELECT DeptId, COUNT(*) AS HighEarnerTally
FROM HighEarners
GROUP
BY DeptId )
SELECT DeptName
FROM DEPARTMENT NATURAL JOIN DeptmentHighEarnerTallies
WHERE HighEarnerTally > 2;
The very early SQL implementations lacked derived tables and HAVINGwas a workaround for one of its most obvious drawbacks (how to select on the result of a set function from the SELECTclause). Once derived tables had become a thing, the need for HAVINGwent away. Sadly, HAVINGitself didn't go away (and never will) because nothing is ever removed from standard SQL. There is no need to learn HAVINGand I encourage fledgling coders to avoid using this historical hangover.
非常早期的 SQL 实现缺少派生表，并且HAVING是其最明显缺点之一的解决方法（如何从SELECT子句中选择 set 函数的结果）。一旦派生表成为一种东西，就不需要HAVING了。可悲的是，HAVING它本身并没有消失（也永远不会消失），因为标准 SQL 中没有任何内容被删除。没有必要学习HAVING，我鼓励初出茅庐的程序员避免使用这种历史遗留问题。
