sql - DB2 SQL 查找第一个和最后一个招聘日期与 deptno 10 相同的部门编号并计算年份

Question

查找与第 10 部门在同一年雇用的部门。假设部门每年招聘 10 人。使用聚合函数和年份函数。

这是我的 SQL：

  select a.deptno, year(a.hiredate) as first, year(b.hiredate) as last, count (*) as num
  from 
  (select d.deptno, h.hiredate, count(h.hiredate)
  from emp as d
  inner join emp as h on
  d.deptno=d.deptno
  where d.deptno = 10 
  group by d.deptno, h.hiredate
  having count (d.deptno)>1
  order by h.hiredate asc
  limit 1) as a

  inner join 

  (select d.deptno, h.hiredate, count(h.hiredate)
  from emp as d
  inner join emp as h on
  d.deptno=h.deptno
  where d.deptno = 10 
  group by d.deptno, h.hiredate
  having count (d.deptno)>1
  order by h.hiredate desc
  limit 1) as b
  on a.deptno=b.deptno
  group by a.deptno, a.hiredate, b.hiredate;

这是我的 SQL 的结果：

 deptno  first  last  num
---------------------------
 10      1980   1982   1

我尝试了不同的方法，例如添加 d.deptno=10 和 d.deptno<>10但结果为空白。以及如何从两个连接中获得正确的计数？如果有人可以帮助我，我会非常高兴。

这是我的表，如果有人想测试它：

 create table emp(
 EMPNO int,
 ENAME varchar(10),
 JOB varchar(20),
 MGR varchar(20),
 HIREDATE date,
 SAL float,
 COM varchar(20),
 DEPTNO int
 );




insert into emp values (7839, 'KING', 'President', '-' , '1981-11-17', 5000.00,'-',10);
insert into emp values(7698, 'BLAKE', 'Manager', 7839, '1981-05-01', 2850.00, '-', 30);
insert into emp values(7782, 'CLARK', 'Manager', 7839, '1981-06-09', 2450.00,'-', 10);
insert into emp values(7756, 'JONES', 'Manager', 7839, '1981-04-02', 2975.00, '-', 20);
insert into emp values(7788, 'SCOTT', 'Analyst', 7566, '1987-04-19', 3000.00, '-', 20);
insert into emp values(7902, 'FORD', 'Analyst', 7566, '1982-02-26', 3000.00, '-', 20);
insert into emp values(7369, 'SMITH', 'Clerk', 7902, '1980-12-17', 800.00, '-', 20);
insert into emp values(7499, 'ALLEN', 'Salesman', 7698, '1981-02-20', 1600.00, 300.00, 30);
insert into emp values(7521,'WARD', 'Salesman', 7698, '1981-02-22', 1250.00, 500.00, 30);
insert into emp values(7654, 'MARTIN', 'Salesman', 7698, '1981-09-28', 1250.00, 1400.00, 30);
insert into emp values(7844, 'TURNER', 'Salesman', 7698, '1981-09-08', 1500.00, 0.00, 30);
insert into emp values(7876, 'ADAMS', 'Clerk', 7798, '1987-05-23', 1100.00, '-', 20);
insert into emp values(7900, 'JAMES', 'Clerk', 7698, '1981-12-03', 950.00, '-', 30);
insert into emp values(7934, 'MILLER', 'Clerk', 7782, '1982-01-23', 1300.00, '-',10);

这是正确的结果，应该显示：

 deptno  first  last  num
---------------------------
 10      1981   1982   2
 20      1981   1982   2

标签： sqljoincountdb2aggregate-functions

Answer 1

如果我正确地跟随你，你可以使用一个公用表表达式来解决这个问题，该表达式计算每个部门的第一个和最后一个雇用年份，然后在具有与部门 10 相同的第一个和最后一个雇用日期的部门中过滤的自联接：

with depts as (
    select 
        deptno, 
        year(min(hiredate)) first_hire_year, 
        year(max(hiredate)) last_hire_year,
        count(*) total_hires
    from emp
    group by deptno
)
select d.*
from depts d
inner join depts d10
    on  d10.deptno = 10
    and d10.first_hire_year = d.first_hire_year
    and d10.last_hire_year = d.last_hire_year

---

另一方面，如果您要抽出所有部门，那么每年当部门 10 确实雇用时，也雇用了，这有点不同。我会生成一个部门 10 雇用的所有年份的列表，将其与部门列表结合起来，然后消除那些不匹配的部门，如下所示：

select 
    d.deptno, 
    min(y.hire_year) first_hire_year,
    max(y.hire_year) last_hire_year,
    count(d.deptno) count_hire_year
from 
    (select distinct year(hiredate) hire_year from emp where deptno = 10) y
    cross join (select distinct deptno from emp) d
    left join (select distinct deptno, year(hiredate) hire_year from emp) e
        on  e.hire_year = y.hire_year
        and e.deptno = d.deptno
group by d.deptno
having count(d.deptno) = count(e.deptno)

在这个关于 DB Fiddle的演示中，第二个查询产生：

部门 | FIRST_HIRE_YEAR | LAST_HIRE_YEAR | COUNT_HIRE_YEAR
-----: | --------------: | -------------: | --------------:
    10 | 1981 | 1982 | 2
    20 | 1981 | 1982 | 2