首页 > 试题广场 >

汇总各个部门当前员工的title类型的分配数目

[编程题]汇总各个部门当前员工的title类型的分配数目

热度指数：310346 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

有一个部门表departments简况如下:

dept_no	dept_name
d001	Marketing
d002	Finance

有一个，部门员工关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1995-12-03	9999-01-01

有一个职称表titles简况如下:

emp_no	title	form_date	to_date
10001	Senior Engineer	1986-06-26	9999-01-01
10002	Staff	1996-08-03	9999-01-01
10003	Senior Engineer	1995-12-03	9999-01-01

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序

dept_no	dept_name	title	count
d001	Marketing	Senior Engineer	1
d001	Marketing	Staff	1
d002	Finance	Senior Engineer	1

示例1

输入

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  titles ;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');

输出

d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1

算法知识视频讲解

西瓜映

不得不说，所有加上当前的这个条件非常的鸡肋
不仅表述不明，而且不考验技术，纯粹拉低AC率

发表于 2017-08-07 22:26:18 回复(33)

Mysql

牛客732681136号

SELECT B.dept_no,B.dept_name,C.title,COUNT(*) AS 'COUNT'
    FROM dept_emp A LEFT JOIN departments B
    ON A.dept_no = B.dept_no AND A.to_date = '9999-01-01'
    JOIN titles C
    ON A.emp_no = C.emp_no AND A.to_date = '9999-01-01'
    GROUP BY dept_no,title
    ORDER BY dept_no

发表于 2021-08-05 13:12:14 回复(0)

Mysql

要学curiosity63

SELECT
c.*,
count(*)
from
(SELECT
a.*,
c.title
from departments a,
     dept_emp b,
     titles c
where a.dept_no=b.dept_no
and b.emp_no=c.emp_no
and b.to_date='9999-01-01'
and c.to_date='9999-01-01'
)c
group by 1,2,3
order by 1

发表于 2021-07-26 15:30:53 回复(1)

Mysql

晓_

SELECT
    t.dept_no,dept_name,title,t.c
FROM
(
SELECT
    dept_no,title,count(title) as c
FROM
    dept_emp d, titles t
WHERE
    d.emp_no=t.emp_no
group BY
    dept_no,title
order by
    dept_no   
) t
left join
    departments d
on
    t.dept_no=d.dept_no

发表于 2021-07-16 22:02:11 回复(0)

吾生有你

select de.dept_no,dp.dept_name,t.title,count(t.title) count from dept_emp de inner join departments dp on
de.to_date='9999-01-01' and de.dept_no=dp.dept_no
inner join titles t on de.emp_no=t.emp_no and t.to_date='9999-01-01'
group by de.dept_no, t.title;

编辑于 2018-07-09 22:10:24 回复(1)

readingilove

select a.dept_no,
       (select dept_name from departments where dept_no = a.dept_no) as 'dept_name',
       b.title,
       count(*) as 'count'
  from dept_emp as a inner join titles as b on a.emp_no = b.emp_no
 where a.to_date = '9999-01-01'
   and b.to_date = '9999-01-01'
 group by a.dept_no, b.title
 order by a.dept_no;

将 dept_emp 和 title 通过 emp_no 进行关联，然后将日期限制为当前日期，再通过 group by 子句就可以得到各个部门的全部头衔（title）。通过使用 count() 函数就可以计算出各种头衔（title）的员工数。最后还要显示部门名称（dept_name），使用标量子查询就可以了。

编辑于 2018-01-20 17:35:20 回复(2)

指offer的剑在哪？

这里在进行分组时需要对两列数据依次进行分组，首先是dept_no部门编号，然后在部门相同时还需要对title标题进行分组；使用INNER JOIN将三个表进行连接

SELECT de.dept_no,dep.dept_name,t.title,count(t.title) FROM 
dept_emp de INNER JOIN titles t ON de.emp_no=t.emp_no 
AND de.to_date='9999-01-01' AND t.to_date='9999-01-01' 
INNER JOIN departments dep ON de.dept_no=dep.dept_no 
GROUP BY de.dept_no,t.title;

发表于 2017-09-05 17:01:05 回复(0)

弘静

select d.dept_no, d.dept_name, t.title, count(t.title) as count from dept_emp de inner join departments d on de.dept_no = d.dept_no inner join

titles t on de.emp_no = t.emp_no and de.to_date = t.to_date and t.to_date = '9999-01-01' group by d.dept_no, t.title

其实难点在于group by进行分组的时候，先从大范围分，也就是部门编号，再在部门内分组

发表于 2017-09-02 11:42:27 回复(0)

eggman

三表连接，然后分组，排序。

分组要用两个字段对应同部门不同title的情况，

最后排序条件如果用了deparment表的dept_no分组，也要用到两个字段，因为牛客是按照dept_no,title升序对比输出结果，只按dept_no排序不通过。

SELECT de1.dept_no,dm1.dept_name,t1.title,count(title) as 'count' 
FROM departments dm1 
JOIN dept_emp de1 ON dm1.dept_no = de1.dept_no
JOIN titles t1 ON de1.emp_no = t1.emp_no 
WHERE t1.to_date='9999-01-01'
    AND de1.to_date='9999-01-01'
    
GROUP BY dm1.dept_name,t1.title
ORDER BY dm1.dept_no,t1.title

编辑于 2020-12-24 14:52:58 回复(0)

牛客325537034号

发表于 2020-08-17 16:33:26 回复(0)

ciphersaw

本题的关键在于用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，具体思路如下：

1、先用 INNER JOIN 连接 dept_emp 与 salaries，根据测试数据添加限定条件 de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'，即当前员工的当前头衔

2、再用 INNER JOIN 连接departments，限定条件为 de.dept_no = dp.dept_no，即部门编号相同

3、最后用 GROUP BY 同时对 de.dept_no 和 t.title 进行分组，用 COUNT(t.title) 统计相同部门下相同头衔的员工个数

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t INNER JOIN dept_emp AS de 
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title

编辑于 2017-07-15 15:42:02 回复(54)

眼泪滑过所有的梦

加上各种条件就完事了，主要是要对dept_no和title进行分组

select b.dept_no,b.dept_name,t.title,count(*) count

from titles t,dept_emp a,departments b
where a.dept_no=b.dept_no
and t.emp_no=a.emp_no
and a.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by b.dept_no,t.title;

发表于 2019-01-17 16:08:45 回复(11)

作业逆流成江

· 刚开始一直通不过，后来看了其他代码，发现 GROUP需要dept_no作为分组条件才行。

· 因为不同部门，包含了不同title，首先是根据部门分组，然后同一部分再根据title分组。

· 下面代码，如果不分组的话，结果是这样的：

"dept_no" "dept_name" "title"

"d001""Marketing" "Senior Engineer"

"d001""Marketing" "Staff"

"d002""Finance" "Senior Engineer"

"d003""Human Resources" "Senior Staff"

"d004""Production" "Senior Engineer"

"d005""Development" "Senior Staff"

"d006""Quality Management""Senior Engineer"

"d006""Quality Management""Engineer"

· 按照先dept_no再 title分组的话，是这样的：

"dept_no""dept_name" "title" "count(de.dept_no)"

"d001" "Marketing" "Senior Engineer" "1"

"d001" "Marketing" "Staff" "1"

"d002" "Finance" "Senior Engineer" "1"

"d003" "Human Resources" "Senior Staff" "1"

"d004" "Production" "Senior Engineer" "2"

"d005" "Development" "Senior Staff" "1"

"d006" "Quality Management" "Engineer" "2"

"d006" "Quality Management" "Senior Engineer" "1"

-----------------------------------------------------------------

SELECT

de.dept_no,

d.dept_name,

t.title,

count(de.dept_no)

FROM

dept_emp de,

departments d,

titles t

WHERE

de.dept_no = d.dept_no

AND de.emp_no = t.emp_no

AND de.to_date = '9999-01-01'

AND t.to_date = '9999-01-01'

GROUP BY

de.dept_no,

t.title

发表于 2017-07-13 15:38:35 回复(2)

小赵冲冲冲！

我发现自从做了上一题，很多逻辑都可以直接用重新建张表来省略中间复杂的关系，不知道这样对程序有什么影响没

select d.dept_no,d.dept_name,emp_title.title,count(emp_title.title) as "count"
from departments d
inner join (select d_p.emp_no,d_p.dept_no,t.title from dept_emp d_p inner join titles t
            on d_p.emp_no = t.emp_no
            where d_p.to_date='9999-01-01' and t.to_date='9999-01-01') as emp_title
on emp_title.dept_no = d.dept_no
group by d.dept_no,emp_title.title;

发表于 2018-04-14 20:09:39 回复(5)

在看面经的孤勇者很纠结

根据条件把三张表用join连接起来，最重要的是group by d.dept_no,t.title，顺序不能变

select d.dept_no,d.dept_name,t.title,count(t.title) as count
from departments d
join dept_emp de
on d.dept_no=de.dept_no
and de.to_date = '9999-01-01'
join titles t
on t.to_date = '9999-01-01'
and de.emp_no=t.emp_no
group by d.dept_no,t.title

发表于 2020-09-23 11:04:29 回复(5)

十月清歌

1、利用inner join 连接title 和dept_emp两张表限定条件为:t.emp_no=de.emp_no

2、利用inner join 连接 departments限定条件为：de.dept_no=dp.dept_no

3、当前员工限定条件为：de.to_date='9999-01-01' and t.to_date='9999-01-01'

4、最后用 group by 同时对 de.dept_no 和 t.title 进行分组

select de.dept_no,dp.dept_name,t.title,count(t.title) as count from titles as t

inner join dept_emp as de on t.emp_no=de.emp_no

inner join departments as dp on de.dept_no=dp.dept_no

where de.to_date='9999-01-01'

and t.to_date='9999-01-01'

group by de.dept_no,t.title;

发表于 2018-04-27 19:08:36 回复(0)

牛客397747951号

-- 这题的话，几个要点
-- 1、要汇总各部门，那么就意味着要GROUP BY 部门
-- 2、要各类型的title数目的count，就意味着要对title进行GROUP BY
-- 3、然后后面那些to_date的条件的话，就单纯是用等于就好了，属于加入条件的废话

-- 所以分析完以后，就是，联结3个表，加条件，然后按dept_no和title分类即可

SELECT departments.dept_no , departments.dept_name ,
titles.title,count(*)
FROM departments , dept_emp , titles
WHERE departments.dept_no = dept_emp .dept_no
AND dept_emp.emp_no = titles.emp_no
AND dept_emp.to_date = '9999-01-01'
AND titles.to_date = '9999-01-01'
GROUP BY departments.dept_no , titles.title

发表于 2020-08-31 17:04:41 回复(0)

Mysql

数分在我心

SELECT de.dept_no, de.dept_name, t.title, count(title)
FROM departments de join dept_emp d 
     on de.dept_no = d.dept_no 
     join titles t on d.emp_no = t.emp_no 
WHERE d.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY de.dept_no, de.dept_name, t.title
ORDER BY dept_no, title

发表于 2022-06-25 19:15:04 回复(0)

Mysql

XXCC111111

select t1.dept_no, dept_name,title, `count`
from
(select dept_no,title,count(title) as `count`
from dept_emp left join titles
using(emp_no)
where dept_emp.to_date = '9999-01-01'
group by dept_no,title) t1
inner join departments t2
on t1.dept_no = t2.dept_no
order by dept_no,title;

发表于 2022-04-06 08:39:07 回复(0)

Mysql

一周后告诉你面试结果

select d.dept_no,d.dept_name,t.title,count(t.title) as count 
from departments d,dept_emp de,titles t 
where d.dept_no=de.dept_no 
and de.emp_no=t.emp_no
and t.to_date='9999-01-01'
group by t.title,d.dept_no
order by d.dept_no,t.title

先将各个表连接起来，并且select后的字段中包含group by后面的字段就不会出错

（注意⚠️select后接聚合字段/group by后的字段/常数都可）

发表于 2022-03-09 17:53:51 回复(0)