查询显示一个表以及哪些行与另一个表匹配的指示 [英] Query which displays a table along with indications of which rows match another table

问题描述

我正在尝试获取一个表中的条形码编号列表，并确定另一表中存在的条形码编号.同样，条形码编号组对应于一个盒子，我还需要知道哪些盒子完全匹配并可以丢弃.不幸的是，条形码编号有时带有多余的非数字字符，因此我在内部联接"ON"语句的两侧都使用了REPLACE函数.下面的查询可以很好地确定table1中的哪些项存在于table2中. (DOCINDEX245是表2上的条形码"列)

I am attempting to take a list of barcode numbers in one table, and determine which ones exist in another table. As well, groups of barcode numbers correspond to a box and I also need to know which boxes fully match and can be disposed of. Unfortunately the barcode numbers sometimes have extraneous non-numeric characters so I'm using the REPLACE function on both sides of the inner join 'ON' statement. The query below works fine to determine which items in table1 exist in table2. (DOCINDEX245 is the 'bar code' column on table2)

SELECT DISTINCT [BAR CODE]
        ,[Box]
  FROM Table1 ft
  INNER JOIN Table2 doc
  ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
  where REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')

我遇到的问题是，如果我尝试查找Table2中不存在的Table1中的行.

Where I run into issues is if I try to find the rows in Table1 which don't exist in Table2.

我尝试过:

SELECT DISTINCT [BAR CODE]
            ,[Box]
      FROM Table1 ft
      LEFT JOIN Table2 doc
      ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
      where REPLACE(doc.DOCINDEX245,'M','') is null

但是它运行了20分钟，没有任何结果，我取消了它.

But it ran for 20 minutes with no results before I cancelled it.

请注意，表1是10列乘60,000行，表2是300列乘140万行.

Note, Table1 is 10 columns by 60,000 rows, Table2 is 300 columns by 1.4 million rows.

我重新整理了DOCINDEX245上的一个索引，但它似乎无能为力，或者还是做不到.

There is an index on DOCINDEX245 which I reorganized but it didn't seem to do anything, or not enough anyway.

总而言之，就像在顶部提到的那样，我需要确定哪些框是完整的"，哪些框具有一些不匹配的行，哪些行是不匹配的.

This all said, as mentioned at the top I then need to determine which boxes are 'complete' and which have some unmatched rows and which rows those are.

所以我的理想结果是:

BARCODE, BOX, MATCH
12345,box1,yes
12346,box1,yes
12347,box1,yes
12348,box2,yes
12349,box2,no
12350,box2,yes

等

我打算尝试使用上述两个查询的结果在Excel中手动创建该查询，但是如果有一个查询可以做到这一点，我将不知所措.

I was going to try to create that manually in Excel with the results of the two queries above, but if there is a single query which could do that I am all ears.

所以我想我在这里有多个问题，所以我愿意接受所有建议.

So I guess I have multiple issues here, so I'm open to all suggestions.

谢谢

推荐答案

尝试一下

SELECT [BAR CODE], [Box], 'yes' as match
  FROM Table1 ft
 INNER JOIN Table2 doc
    ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
union
SELECT [BAR CODE], [Box], 'no'
  FROM Table1 ft
  LEFT JOIN Table2 doc
    ON REPLACE(REPLACE(ft.[BAR CODE],'M',''),'-WDRN','') = REPLACE(doc.DOCINDEX245,'M','')
 WHERE doc.DOCINDEX245 is null;

这篇关于查询显示一个表以及哪些行与另一个表匹配的指示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！