使用 Access sql 从 2 个表中查找不匹配的记录 [英] Find unmatched records from 2 tables using Access sql

问题描述

我正在尝试使用以下查询(查询是从访问查询向导构建的)在两个表中查找不匹配的记录(两个表中都包含 100 多条记录)

I'm trying to find unmatched records in both tables(contains more than 100 records in both) using below query (query was built from access query wizard)

表A

    EC ID   wt
     1001   339.55
     1007   3.77
     1008   1.76
     1009   13.48
     1010   5.86
     1011   11.58
     1012   37.89
     1013      4.88
     1015      6.98

表 B

     EC ID  wt_xxxx
     1001   339.55
     1002      1.99
     1003      1.78
     1004       2
     1007   3.77
     1008   1.76
     1009   13.48
     1010   5.86
     1011   11.58
     1012   37.89

Query1 返回表 A 中所有不在表 B 中的记录.

Query1 retruns all records in table A those are not in table B.

  SELECT A.[EC ID], A.wt 
  FROM A LEFT JOIN B ON A.[EC ID] = B.[EC ID]
   WHERE (((B.[EC ID]) Is Null));

退货

   EC ID    wt
   1013      4.88
   1015      6.98

同样，Query2 会返回表 B 中所有不在表 A 中的记录.

Similarly Query2 retruns all records in table B those are not in table A.

  SELECT B.[EC ID], B.wt_xxxx
  FROM B LEFT JOIN A ON B.[EC ID] =A.[EC ID]
  WHERE (((A.[EC ID]) Is Null));

退货

    EC ID    wt_xxxx
     1002      1.99
     1003      1.78
     1004       2

运行第二个查询时，访问正在关闭.但有时它会给出结果.不明白为什么.有什么有效的方法可以做到这一点.我在stackoverflow中使用了一个查询用 SQL 查找不匹配的记录 性能很差，需要 15 分钟才能得到结果.不匹配的记录查询还有其他解决方案吗?

when running second query the access is closing itself . But in sometimes it gives result . Couldn't understand why. is there any efficient way to do it. i used a query in stackoverflow Finding unmatched records with SQL performance is very poor it takes 15min to get result. Any other solution is there with unmatched records query?

推荐答案

如果一条记录在一个表中只出现一次

if a record will only appear once in a table

select count(*),EC ID,wt_xxxx
from (select B.[EC ID], B.wt_xxxx
      TABLEB B
      union all
      select A.[EC ID], A.wt_xxxx
      TABLEB A) q
group by EC ID,wt_xxxx
having count(*) =1

或使用你所拥有的.你很接近，只需使用完整的外连接在任一表中查找空值

OR using what you have. You are close, just use a full outer join to look for nulls in either table

  SELECT B.[EC ID], B.wt_xxxx, A.[EC ID], A.wt_xxxx, 
  FROM B FULL OUTER JOIN A ON B.[EC ID] =A.[EC ID]
  WHERE A.[EC ID] Is Null or B.[EC ID] Is Null  ;

这篇关于使用 Access sql 从 2 个表中查找不匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！