SQL 连接 : Select status of reviews submitted by employees and also the list of employees who have not submitted the review for each year

Question

对于每一年，对于每一位员工，我想列出员工提交的审核状态，或者“未启动”，以防员工当年没有提交审核。

用文字表达这个问题有点困难，所以我会尝试通过举例来解释它:

create table #employees 
(
empid int,
name varchar(100)
)

Create table #review
(
empid int,
ryear int,
status varchar(20)
)

insert into #review values(1,2016,'S2')
insert into #review values(2,2016,'S2')
insert into #review values(2,2017,'S1')
insert into #review values(3,2017,'S2')



insert into #employees values(1,'jack')
insert into #employees values(2,'mack')
insert into #employees values(3,'rack')
insert into #employees values(4,'tack')

查询错误

select a.empid
      ,a.name
      ,b.ryear
      ,case isnull(b.status,'')
           when ''
               then 'Not Initiated'
           else status
       end as status
from #employees as a
    left join #review as b
        on a.empid = b.empid
           and b.ryear in(select distinct
                                 ryear
                          from #review
                         );--something like that

预期结果:

+-------+------+-------+----------------+
| empid | name | ryear |     status     |
+-------+------+-------+----------------+
|     1 | jack |  2016 | S2             |
|     1 | jack |  2017 | not initiated  |
|     2 | mack |  2016 | S2             |
|     2 | mack |  2017 | S1             |
|     3 | rack |  2016 | not initieated |
|     3 | rack |  2017 | S2             |
|     4 | tack |  2016 | Not Initiated  |
|     4 | tack |  2017 | Not Initiated  |
+-------+------+-------+----------------+

Answer 1

您可以在子查询上使用交叉连接

select a.empid
      ,a.name
      ,c.ryear
      ,isnull(b.status,'Not Initiated') as status
from #employees as a
    cross join(select distinct
                      ryear
               from #review
              ) as c
    left join #review as b
            on b.ryear = c.ryear
            and a.empid = b.empid
order by a.empid, ryear