极客教程SQL 为值区间填充缺失值，你想列出整个 20 世纪 80 年代里每年新入职的员工人数，但有一些年份并没有新增员工。你希望返回如下所示的结果集。
SQL 为值区间填充缺失值 问题描述
你想列出整个 20 世纪 80 年代里每年新入职的员工人数，但有一些年份并没有新增员工。你希望返回如下所示的结果集。
YR CNT
---- ----------
1980 1
1981 10
1982 2
1983 1
1984 0
1985 0
1986 0
1987 0
1988 0
1989 0
SQL 为值区间填充缺失值 解决方案
本解决方案的关键之处在于如何为那些没有新增员工的年份返回 0。如果在一个给定的年份里没有新入职的员工，那么 EMP 表里就不存在对应的行。既然表里不包含这一年，我们该如何为这一年返回计数值 0 呢？本解决方案需要用到外连接操作。我们要拼凑一个包含了所有目标年份的结果集，然后针对 EMP 表执行 COUNT 查询，以判断每一年里是否新增了员工。
DB2
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 YEAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。
1 select x.yr, coalesce(y.cnt,0) cnt
2 from (
3 select year(min(hiredate)over()) -
4 mod(year(min(hiredate)over()),10) +
5 row_number()over()-1 yr
6 from emp fetch first 10 rows only
7 )x
8 left join
9 (
10 select year(hiredate) yr1, count(*) cnt
11 from emp
12 group by year(hiredate)
13 ) y
14 on ( x.yr = y.yr1 )
Oracle
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 TO_NUMBER 和 TO_CHAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。
1 select x.yr, coalesce(cnt,0) cnt
2 from (
3 select extract(year from min(hiredate)over()) -
4 mod(extract(year from min(hiredate)over()),10) +
5 rownum-1 yr
6 from emp
7 where rownum <= 10
8 ) x,
9 (
10 select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
11 from emp
12 group by to_number(to_char(hiredate,'YYYY'))
13 ) y
14 where x.yr = y.yr(+)
如果使用的是 Oracle 9i 及后续版本，则不妨使用新提供的 JOIN 子句。
1 select x.yr, coalesce(cnt,0) cnt
2 from (
3 select extract(year from min(hiredate)over()) -
4 mod(extract(year from min(hiredate)over()),10) +
5 rownum-1 yr
6 from emp
7 where rownum <= 10
8 ) x
9 left join
10 (
11 select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
12 from emp
13 group by to_number(to_char(hiredate,'YYYY'))
14 ) y
15 on ( x.yr = y.yr )
PostgreSQL 和 MySQL
把 T10 表作为数据透视表（因为它有 10 行数据），并调用内置函数 EXTRACT，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。
1 select y.yr, coalesce(x.cnt,0) as cn
2 from (
3 select min_year-mod(cast(min_year as int),10)+rn as yr
4 from (
5 select (select min(extract(year from hiredate))
6 from emp) as min_year,
7 id-1 as rn
8 from t10
9 ) a
10 ) y
11 left join
12 (
13 select extract(year from hiredate) as yr, count(*) as cnt
14 from emp
15 group by extract(year from hiredate)
16 ) x
17 on ( y.yr = x.yr )
SQL Server
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 YEAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。
1 select x.yr, coalesce(y.cnt,0) cnt
2 from (
3 select top (10)
4 (year(min(hiredate)over()) -
5 year(min(hiredate)over( ))%10)+
6 row_number()over(order by hiredate)-1 yr
7 from emp
8 ) x
9 left join
10 (
11 select year(hiredate) yr, count(*) cnt
12 from emp
13 group by year(hiredate)
14 ) y
15 on ( x.yr = y.yr )
SQL 为值区间填充缺失值 扩展知识
尽管各个数据库的解决方案在语法上有所不同，做法却并无二致。内嵌视图 X 先找出最早的 HIREDATE 值对应的年份，进而返回 20 世纪 80 年代的每一年。下一步是用最早的年份减去该年份模 10 计算的结果，然后再加上 RN–1。为了更清楚地了解其工作原理，我们不妨实际运行一下内嵌视图 X，并分别返回其中涉及的每一个值。下面列出了两个版本的内嵌视图 X，它们分别使用窗口函数 MIN OVER（DB2、Oracle 和 SQL Server）和标量子查询（MySQL 和 PostgreSQL）来得到对应的结果集。
select year(min(hiredate)over()) -
mod(year(min(hiredate)over()),10) +
row_number()over()-1 yr,
year(min(hiredate)over()) min_year,
mod(year(min(hiredate)over()),10) mod_yr,
row_number()over()-1 rn
from emp fetch first 10 rows only
YR MIN_YEAR MOD_YR RN
---- ---------- ---------- ----------
1980 1980 0 0
1981 1980 0 1
1982 1980 0 2
1983 1980 0 3
1984 1980 0 4
1985 1980 0 5
1986 1980 0 6
1987 1980 0 7
1988 1980 0 8
1989 1980 0 9
select min_year-mod(min_year,10)+rn as yr,
min_year,
mod(min_year,10) as mod_yr
rn
from (
select (select min(extract(year from hiredate))
from emp) as min_year,
id-1 as rn
from t10
) x
YR MIN_YEAR MOD_YR RN
---- ---------- ---------- ----------
1980 1980 0 0
1981 1980 0 1
1982 1980 0 2
1983 1980 0 3
1984 1980 0 4
1985 1980 0 5
1986 1980 0 6
1987 1980 0 7
1988 1980 0 8
1989 1980 0 9
内嵌视图 Y 返回每一个 HIREDATE 对应的年份，以及那一年新增的员工人数。
select year(hiredate) yr, count(*) cnt
from emp
group by year(hiredate)
YR CNT
----- ----------
1980 1
1981 10
1982 2
1983 1
最后，外连接内嵌视图 Y 到内嵌视图 X，这样即使某一年没有新增员工，也会返回这一年的计数结果。