mysql - 在 MySQL 中查找序列中缺失的数据

Question

有没有一种有效的方法不仅可以在一个序列中找到丢失的数据，还可以在许多序列中找到丢失的数据？

这可能不可避免地是 O(N**2)，因此这里的高效被定义为使用 MySQL 的相对较少的查询

假设我有一张临时员工表及其开始和结束月份。

employees  | start_month | end_month
------------------------------------
Jane         2017-05       2017-07
Bob          2017-10       2017-12

还有一张相关的每月向这些员工付款的表格

employee | paid_month
---------------------
Jane       2017-05
Jane       2017-07
Bob        2017-11
Bob        2017-12

现在，很明显我们错过了 Jane (2017-06) 的一个月和 Bob (2017-10) 的一个月。

有没有一种方法可以找到他们付款记录中的空白，而无需多次来回？

在只有一个序列需要检查的情况下，有些人会生成一个有效值的临时表，然后使用 LEFT JOIN 来查找间隙。但这里我们对每个员工都有不同的顺序。

一种可能性是，我们可以执行聚合查询来查找每个员工的paid_months COUNT()，然后将其与预期的月份增量进行检查。不幸的是，这里的数据有点脏，所以我们实际上的付款日期可能在员工开始或结束日期之前或之后。但我们正在验证官方序列确实有付款。

Answer 1

形成员工和月份的笛卡尔积，然后将实际数据加入其中，然后当笛卡尔积没有匹配的付款时，就会显示丢失的数据。

您需要每个月的列表。这可能来自您已有的“日历表”，或者，如果每个月都在源数据中表示，则可能可以使用子查询)

例如

select
    m.paid_month, e.employee 
from (select distinct paid_month from payments) m
cross join (select employee from employees) e
left join payments p on m.paid_month = p.paid_month and e.employee = p.employee
where p.employee is null

子查询m可以用日历表或其他生成一系列月份的技术来代替。例如

select 
        DATE_FORMAT(m1, '%Y-%m')
from (
        select 
              '2017-01-01'+ INTERVAL m MONTH as m1
        from (
            select @rownum:=@rownum+1 as m 
            from (select 1 union select 2 union select 3 union select 4) t1
            cross join (select 1 union select 2 union select 3 union select 4) t2
            ## cross join (select 1 union select 2 union select 3 union select 4) t3
            ## cross join (select 1 union select 2 union select 3 union select 4) t4
            cross join(select @rownum:=-1) t0
            ) d1
        ) d2 
where m1 < '2018-01-01'
order by m1

子查询e可以包含其他逻辑(例如，确定哪些员工当前仍在受雇，或者哪些员工是“临时员工”)