SQL 员工部门明智，员工人数超过 5
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24612348/
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
employee department wise and count of employees more than 5
提问by rajeshrepalle
i want to display department_id's along with count,and count should be more than 5, and i want to have employees who are not hired in January.
我想department_id和count一起显示's，count应该大于5，我想有1月份没有被录用的员工。
i tried the below query
我试过下面的查询
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(
SELECT DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>5
)
AND HIRE_DATE NOT LIKE '%JAN%';
but here I didnt get count.I want count Also.
但在这里我没有得到计数。我也想要计数。
回答by evanv
If you want the dept id and count of employees (where employee hire date is not in Jan) then something like the following should work. I say "something like the following" because I suspect the WHERE hire_date NOT LIKE '%JAN%' could be improved, but it would just depend on the format of that column.
如果您想要部门 ID 和员工人数（员工雇用日期不在 1 月），则应该使用以下内容。我说“类似于以下内容”是因为我怀疑 WHEREhiring_date NOT LIKE '%JAN%' 可以改进，但这仅取决于该列的格式。
SELECT
DEPARTMENT_ID,
COUNT(*)
FROM EMPLOYEES
WHERE HIRE_DATE NOT LIKE '%JAN%'
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>5;
If you also want to list the individual employees along with these departments, then something like this might work:
如果您还想列出各个员工以及这些部门，那么这样的事情可能会奏效：
SELECT a.*, b.count(*)
FROM EMPLOYEES AS a
INNER JOIN (
SELECT
DEPARTMENT_ID,
COUNT(*)
FROM EMPLOYEES
WHERE HIRE_DATE NOT LIKE '%JAN%'
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>5) AS b
ON a.department_id = b.department_id
WHERE a.HIRE_DATE NOT LIKE '%JAN%';
Again, though, I think you can leverage your schema to improve the where clause on HIRE_DATE. A like/not-like clause is generally going to be pretty slow.
尽管如此，我认为您可以利用您的架构来改进 HIRE_DATE 上的 where 子句。一个like/not-like子句通常会很慢。
回答by CSharper
SELECT department_ID, count(employee_id) as '# of Employees' FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(
SELECT DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*)>5
)
AND HIRE_DATE NOT LIKE '%JAN%'
group by department_ID;
This query returns the department_id and because I group by department_id, the count of employees that belong to each department will be returned
此查询返回department_id，因为我按department_id 分组，因此将返回属于每个部门的员工数
Output will look something like this
输出看起来像这样
Department_Id | # of Employees
1 7
2 6
4 9
回答by Bohemian
Select the count from your inner query and jointo it:
从内部查询中选择计数并加入它：
SELECT E.*, DEPT_COUNT
FROM EMPLOYEES E
JOIN (
SELECT DEPARTMENT_ID, COUNT(*) DEPT_COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 5
) DC ON E.DEPARTMENT_ID = DC.DEPARTMENT_ID
AND HIRE_DATE NOT LIKE '%JAN%'
