根据列数据查找丢失的行-MySQL [英] FInd missing row based on Column data -MySQL

问题描述

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

I am currently working on a.Net web form solution which generates a brief service report for admins to monitor the services done by technicians.As of now , i am having some trouble in coming up with an efficient SQL (for MySQl) which return data rows along with the missing rows based on the SertvicePrtNum , which is in order. For Example :- This is my raw data in the table :-

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 在表中缺失.所以我希望数据库将结果返回为: -

Here the ServiceRptNum , 1004 is missing in the table. So i want the db to return the result as : -

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.

Here , the sql additionally generated 1004,1006,1008,1009 since it cannot find those records.

请注意，插入数据时会自动生成(auto_increment)Id.但 Service ReportNum 不是，这是为了让管理员稍后使用手动生成的报告 Num(report num in公司服务手册的硬拷贝).

Please note that the Id is automatically generated (auto_increment)while insert of the data.But the Service ReportNum is not , this is to enable the admin to add the service report later on with the manually generated report Num (report num in the hardcopy of the company Servicebook).

推荐答案

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

You basically need to invent a constant, sequential stream of numbers and then left join your real data to them. For this method to work, you need a table with enough rows in it to generate a counter big enough:

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 的表的名称

You need to alter some things in the code above because you never told us the name of your table with missing rptnums. You also need to utilise another table in your database with more rows than this table because the way this method works is to count the rows in the bigger table, giving each a number. If you don't have any table bigger than this one, we can probably get enough rows by cross joining a smaller table to itself or by using this table. Replace somebigtable with thistable CROSS JOIN thistable where this table is the name of the table with missing servicerptnums

如果你只想要丢失的行，在sql的末尾添加一个WHERE servicerptnum is null

If you want just the rows that are missing, add a WHERE servicerptnum is null to the end of the sql

编辑，我看到您更改了编号:

Edit, I see you've changed your numbering from:

1001
1002
...
1009
10010

致:

1009
1010

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

The join condition used to be servicerptnum = concat('100', cast(n as varchar)), it is now servicerptnum = 1000+n..

这篇关于根据列数据查找丢失的行-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！