将第一个表中的非存在记录与第二个表内连接相匹配

How do I match records in first table that don't exist to records in second table on the inner join clause. For example, if I am inner joining based on office numbers but office number 90 does not exist in second table, but the closest office is 91 then how would I 'substitute' or make that match?

如何将第一个表中不存在的记录与内部连接子句的第二个表中的记录进行匹配。例如,如果我是基于办公室号码的内部加入,但第二个表中不存在办公室号码90,但是最近的办公室是91,那么我将如何“替换”或进行匹配?

if office='90' then where in office in ('91')

if office = '90'然后在办公室里面('91')

2 个解决方案

#1

You would need to create a separate mapping table and join with that. Something like this:

您需要创建一个单独的映射表并与之连接。像这样的东西:

OfficeNumberMap
  - OfficeNumber
  - SubstituteOfficeNumber

So in that table you would have an entry for every "valid" office number mapping to itself, and entries for an "invalid" office number mapping to the substitute.

因此,在该表中,您将有一个条目用于映射到自身的每个“有效”办公室编号,以及映射到替换的“无效”办公室编号的条目。

Office Number | SubstituteOfficeNumber
--------------+-----------------------
            80                      80  <- valid
            81                      81  <- valid
            90                      91  <- Invalid, so use 91 as alternate.

#2

You would think that finding the closest numeric match in a list would be something easily expressed in SQL. And there is a way, although the exact syntax varies by database:

你会认为在列表中找到最接近的数字匹配将是在SQL中容易表达的东西。有一种方法,虽然确切的语法因数据库而异:

select ft.officenumber,
       (select OfficeNumber from SecondTable st order by abs(ft.OfficeNumber - st.OfficeNumber limit 1
       ) as MyBestGuess
from FirstTable ft

What this is doing is using a correlated subquery to look through the second table. It is ordering all the results by the absolute value of the difference and then choosing the first value.

这样做是使用相关子查询来查看第二个表。它按差值的绝对值排序所有结果,然后选择第一个值。

A couple comments. In any SQL engine I can think of, this will require a full table scan of SecondTable for every row of FirstTable. Translation: it is slow and will get much slower faster as the amount of data increases.

几个评论。在我能想到的任何SQL引擎中,这将需要对FirstTable的每一行进行SecondTable的全表扫描。翻译:它很慢,随着数据量的增加,速度会慢得多。

Second, this only works on numerical values. I don't know what "close" means for character strings.

其次,这仅适用于数值。我不知道“close”对于字符串意味着什么。

Third, limit is common syntax to get only the first row of output. It works in most databases. In SQL Server, you would use select top 1 and in Oracle, you would use where rownum = 1.

第三,限制是仅获得第一行输出的常用语法。它适用于大多数数据库。在SQL Server中,您将使用select top 1,在Oracle中,您将使用rownum = 1。

#1