首页 > 试题广场 >

统计各个部门的工资记录数

[编程题]统计各个部门的工资记录数
  • 热度指数:346177 时间限制: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
2001-06-22 9999-01-01
10002 d001
1996-08-03 9999-01-01
10003 d002
1996-08-03 9999-01-01

有一个薪水表salaries简况如下:
emp_no 
salary
from_date 
to_date
10001
85097 2001-06-22
2002-06-22
10001 88958 2002-06-22 9999-01-01
10002
72527 1996-08-03
9999-01-01
10003
32323 1996-08-03 9999-01-01


请你统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum,按照dept_no升序排序,以上例子输出如下:
dept_no dept_name sum
d001 Marketing 3
d002 Finance 1
示例1

输入

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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 `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

输出

d001|Marketing|3
d002|Finance|1
我是这么想的,有涨幅应该就是有变动吧,涨幅也可以是负数扒,然后有变动,那么数据就会多,那么直接查个数就好了
我们先将salaries表和dept_emp 两张表连接起来得到一张新的表再把第一张表和新表连接起来就可以得到了,运行成功了的
select aa.dept_no,dd.dept_name,aa.a as 'sum'
from (select dept_no,count(salary) as a
        from dept_emp d
        inner join salaries s on d.emp_no=s.emp_no
        group by d.dept_no) aa
inner join departments dd on dd.dept_no =aa.dept_no
发表于 2020-05-17 12:31:58 回复(0)
我觉得这道题通过的代码是有问题的,我的思路和我觉得正确的代码应该如下。
1、先计算每个员工的涨幅次数,先group by,再去重,最后计数,计数完后再减1(比如说去重后有2条记录,说明涨幅了一次,所以最后要减1)
2、再按正常的方法使用左连接将三个表连接起来。
select dm.dept_no,dm.dept_name,count(sa.e_sum) sum
from departments dm 
left join dept_emp de on dm.dept_no=de.dept_no
left join 
(select emp_no,count(distinct(salary))-1 e_sum from salaries group by emp_no) sa 
on sa.emp_no=de.emp_no
group by dm.dept_no;


发表于 2019-08-21 17:09:37 回复(1)
 解题思路:
                1、先查出薪资表中员工每次薪资发放不同的薪资,用 员工no  group by 分组去重 ,统计涨幅次数,得出 A表
                2、然后用  dept_emp 表连接A表 查询出 dept_no 和每个部门的涨幅次数sum(总和) 用 dept_emp 中的dept_no分组得出 B表
                3、最后用departments 表连接 B表 查询出 B表中的 dept_no、sum 和 departments 表中的 dept_name
                
发表于 2017-11-28 12:06:01 回复(0)
select d.dept_no, dept_name,t3.snums as sum from 
    (
        select dept_no as jid1, count(s.emp_no) as snums from salaries s 
        inner join 
        dept_emp dm 
        on s.emp_no=dm.emp_no group by dept_no 
    ) t3 
    inner join 
    departments d 
    on t3.jid1=d.dept_no
上述SQL通过,其结果是各个部门在salaries表中有多少条记录
而下面的代码没有通过:
select d.dept_no, dept_name,t3.snums-t3.enums as sum from 
    ( 
        select t1.jid1, t1.snums, t2.enums from 
            (
                select dept_no as jid1, count(s.emp_no) as snums 
                from salaries s 
                inner join dept_emp dm 
                on s.emp_no=dm.emp_no group by dept_no 
            ) t1 
            inner join 
            (
                select dept_no as jid2, count(distinct(emp_no)) as enums 
                from dept_emp group by dept_no 
            ) t2 
            on t1.jid1=t2.jid2 
        ) t3 
    inner join departments d 
    on t3.jid1=d.dept_no
这条SQL是把每个部门的工资记录数减去该部门员工数作为涨幅次数(没有考虑涨幅为0或者工资下降的情况)

发表于 2017-08-23 15:40:14 回复(0)
本题关键是要 每个部门分组,并分别统计工资记录总数,思路如下
1、用INNER JOIN连接dept_emp表和salaries表,并以dept_emp.no分组,统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表,限制条件为两表的dept_no相等,找到dept_no与dept_name的对应关系,最后依次输出dept_no、dept_name、sum
SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no) 
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no

编辑于 2017-07-15 09:36:07 回复(76)

这样做虽然没有ac,但是符合题意,比直接查询所有工资情况有意义

select 
    d.dept_no,
    d.dept_name,
    (select 
         sum((select 
                 sum((select 
                     case
                         # 记录数为0说明是第一条记录(原来的答案里有这一条,测试后发现加上去答案和预期不符)
                         # when count(*) = 0 then 0
                         # 最近一次工资变化比当前工资低判定为涨工资
                         when s0.salary < s.salary then 1
                         # 其他情况判定为不是涨工资
                         else 0 end
                     # 查询最近一次工资变化情况
                     from salaries s0 where s0.emp_no = s.emp_no and s0.to_date < s.to_date order by s0.to_date desc limit 1))
             # 查询出每个成员的每次工资变化情况
             from salaries s where s.emp_no = de.emp_no))
     # 查询出部门中的每个成员
     from dept_emp de where de.dept_no = d.dept_no) as sum 
from departments d;
编辑于 2018-02-27 23:18:17 回复(19)
select 
    dm.dept_no,dm.dept_name,count(*) sum
from 
    departments dm,dept_emp de,salaries s
where
    dm.dept_no=de.dept_no
and 
    de.emp_no=s.emp_no
group by 
    dm.dept_no

发表于 2018-09-29 10:25:11 回复(29)
/*
一定要明白一步一步来求得所需要的数据!!!!
思路:
1.查询每个员工所的涨幅次数:select emp_no,count(emp_no) as counts from salaries group by emp_no
2.然后与dept_emp表连接,生成一个包含dept_no,emp_no,counts字段的临时表:
    select de.dept_no,de.emp_no,en.counts from dept_emp de inner join (select emp_no,count(emp_no) as counts from salaries group by emp_no) as en
        on de.emp_no = en.emp_no
3.最后将第二步生成的临时表再和departments进行连接,以dept_no分组,计算对于部门的所有员工的counts的总和,生成字段为题目所需的
*/
select d.dept_no,d.dept_name,sum(dS.counts) from departments d inner join 
    (select de.dept_no,de.emp_no,en.counts from dept_emp de inner join (select emp_no,count(emp_no) as counts from salaries group by emp_no) as en
    on de.emp_no = en.emp_no) as ds on d.dept_no = ds.dept_no group by d.dept_no;
发表于 2017-11-15 15:10:51 回复(7)
select d.dept_no,count(s.salary) sum
from departments d inner join dept_emp de
on d.dept_no = de.dept_no
inner join salaries s 
on de.emp_no = s.emp_no
group by d.dept_no
先聚合分组,注意
dept_name
不是聚合分组键,也不是聚合函数,不可以在分组的情况下使用
再和的一个表departments连接就可以了

select d1.dept_no, d1.dept_name,sum
from departments d1 inner join 
(select d.dept_no,count(s.salary) sum
from departments d inner join dept_emp de
on d.dept_no = de.dept_no
inner join salaries s 
on de.emp_no = s.emp_no
group by d.dept_no) x
on d1.dept_no = x.dept_no
order by d1.dept_no asc

编辑于 2021-04-12 20:49:03 回复(0)
  • 根据题意,各部门 = GROUP BY d.dept_no
  • 工资次数 = COUNT(salary)
  • COUNT()GROUP BY可以统计列的次数。
    SELECT d.dept_no, d.dept_name, COUNT(salary) sum FROM departments d,dept_emp de, salaries s
    WHERE d.dept_no = de.dept_no
    AND de.emp_no = s.emp_no
    GROUP BY d.dept_no
编辑于 2020-01-02 20:21:34 回复(4)
select a.dept_no, a.dept_name, count(c.salary) as sum
from departments as a 
left join dept_emp b 
on a.dept_no = b.dept_no
left join salaries as c
on b.emp_no = c.emp_no
group by a.dept_no
一段平平无奇的粗暴三段join代码
发表于 2020-08-10 21:17:37 回复(8)
select d.dept_no,d.dept_name,count(*) sum
from departments d,salaries s,dept_emp de where d.dept_no = de.dept_no and de.emp_no = s.emp_no
group by d.dept_no;
我直接把三个表连接起来也能通过...

发表于 2017-10-07 11:36:27 回复(2)
select dd.dept_no,dd.dept_name,count(s.salary) 
from ( dept_emp join departments on dept_emp.dept_no = departments.dept_no ) as dd
join salaries as s
on dd.emp_no = s.emp_no
group by dd.dept_no 


关键是先根据dept_no分组得到部门所有的涨薪记录,然后用count计数。
发表于 2017-08-29 23:55:24 回复(1)
select d.dept_no,d.dept_name, count(salary) as sum  //简洁明了
from departments d, dept_emp de, salaries s
where d.dept_no = de.dept_no
and de.emp_no = s.emp_no
group by d.dept_no;
发表于 2017-07-08 17:56:17 回复(6)
select d.dept_no,d.dept_name,count(d.dept_no) from departments d
left join dept_emp de on d.dept_no=de.dept_no
left join salaries s on s.emp_no=de.emp_no
group by d.dept_no
order by d.dept_no asc;
发表于 2021-11-22 11:39:42 回复(0)
题目不严谨,如果有员工在部门之间跳槽的情况呢,还是我想太复杂了?
发表于 2019-06-17 09:51:34 回复(3)
select aa.dept_no, aa.dept_name, sum(bb.ptime) as sum
from (
    (
    	dept_emp as a
    	left join departments as b
    	on a.dept_no = b.dept_no
    ) as aa
	left join (
    	select emp_no, count(salary) as ptime
        from salaries
        group by emp_no
    ) as bb
    on aa.emp_no = bb.emp_no
    )
group by aa.dept_no
这段代码是可以通过的。此题中,salary表中出现多少条记录就算多少次涨幅,个人认为这不够严谨。
发表于 2017-08-15 15:40:56 回复(10)
select c.dept_no, c.dept_name, count(*) as sum
from salaries a 
left join dept_emp b on b.emp_no = a.emp_no
left join departments c on c.dept_no = b.dept_no
group by c.dept_no
发表于 2022-04-15 15:42:17 回复(0)
select d1.dept_no,d1.dept_name,
count(salary) as [sum]
from departments as d1
left join dept_emp as d2
on d1.dept_no=d2.dept_no
left join salaries as s
on d2.emp_no=s.emp_no
group by d1.dept_no,d1.dept_name
order by d1.dept_no
  • 先连接员工表和部门表,再用group by  进行分组即可得到答案
发表于 2022-02-06 15:57:09 回复(0)
丑陋的代码,通过了
select dp.dept_no
,dp.dept_name
,count(a.salary) sum
from(
    select de.dept_no
    ,sa.emp_no
    ,sa.salary
    from dept_emp de
    right join salaries sa
    on de.emp_no = sa.emp_no
    #group by de.dept_no
) a
right join departments dp
on a.dept_no = dp.dept_no
group by dp.dept_no
order by dp.dept_no;


发表于 2021-09-05 16:03:18 回复(0)