mysql - 根据列数据查找缺失行-MySQL

Question

我目前正在开发一个.Net Web 表单解决方案，该解决方案为管理员生成一份简短的服务报告，以监控技术人员完成的服务。到目前为止，我在提出高效的 SQL(适用于 MySQl)方面遇到了一些麻烦它返回数据行以及基于 SertvicePrtNum 的缺失行(按顺序排列)。例如 :-这是我在表中的原始数据:-

Id    ServiceRptNum  Customer_ID  Date of Service 
----  -------------  -----------  ---------------
1      1001              3        09/10/1997
2      1003              8        10/06/2005 
3      1005              1        21/02/2003
4      1007              7        1/06/2011
5      1010              4        4/11/2012
6      1002              2        16/01/2003

这里表中缺少 ServiceRptNum 、 1004 。所以我希望数据库返回结果为: -

Id    ServiceRptNum  Customer_ID  Date of Service 
----  -------------  -----------  ---------------
1      1001              3        09/10/1997
2      1002              2        16/01/2003
3      1003              8        10/06/2005 
-      1004              -            - 
4      1005              1        21/02/2003
-      1006              -            -
5      1007              7        1/06/2011
-      1008              -            - 
-      1009              -            -
6      1010              4        4/11/2012

这里，sql 额外生成了 1004,1006,1008,1009，因为它找不到这些记录。

请注意，ID 是在插入数据时自动生成的(auto_increment)。但是 Service ReportNum 不是，这是为了使管理员能够稍后使用手动生成的报告 Num 添加服务报告(报告编号 in公司服务手册的硬拷贝)。

Answer 1

您基本上需要发明一个恒定的、连续的数字流，然后将您的真实数据左连接到它们。为了使此方法发挥作用，您需要一个包含足够行的表来生成足够大的计数器:

select ID, 1000+n as servicerptnum, customer_id, `Date of Service` from
(
 SELECT  @curRow := @curRow + 1 AS n
 FROM    somebigtable 
 JOIN    (SELECT @curRow := 0) r
 WHERE   @curRow<100
) numbergen
LEFT JOIN
tablewithmissingservicerptnum
ON
  servicerptnum = 1000+n

您需要更改上面代码中的一些内容，因为您从未告诉我们缺少 rptnum 的表的名称。您还需要利用数据库中的另一个表，其行数比该表多，因为此方法的工作方式是对较大表中的行进行计数，并为每个行提供一个数字。如果没有比这个表更大的表，我们可能可以通过将一个较小的表交叉连接到自身或使​​用该表来获得足够的行。将 somebigtable 替换为 thistable CROSS JOIN thistable，其中此表是缺少 servicerptnums 的表的名称

如果您只需要缺失的行，请在 sql 末尾添加 WHERE servicerptnum is null

编辑，我发现您已将编号更改为:

1001
1002
...
1009
10010

致:

1009
1010

连接条件原来是servicerptnum = concat('100',cast(n as varchar))，现在是servicerptnum = 1000+n..