如何查找相似记录？再次发帖征集正确答案。

Question

有两张数据表，tb1(已有数据表)和tb2(待比较数据表)，欲进行信息比较，要求如下： 
1）定义比较的若干属性字段 如：fld1,fld2,fld3
2）从tb2表中查询出其在tb1中近似的记录信息（只有部分属性字段匹配）。说明：1.两表的结构完全相同，只是数据有可能不同。
2.请回答者确保你的回答是可执行的，正确的，因为该问题以前有朋友回答过，也得了分，后来发现
语法上虽然没问题，语义上也对，但真正执行时结果却是错误的。比如以下朋友：
of123(所以然) 的回答就是错误的，根本实现不了。 
3.回答的SQL语句中请不要出现 IN 或者NOT IN。因为其效率太差，请给出高效率的SQL语句。
4.最好能把你的代码自己运行一下，否则可能是不对的，尤其是使用EXISTS或NOT EXISTS 加不等于符号 <>的朋友
请注意，因为我也是这样做的，但结果出乎意料。
5.完贴必高分相送。

Answer 1

上个帖子的2楼已经回复过了
http://topic.csdn.net/u/20100208/14/b833feef-5845-4cc9-a09b-e4a37f4865fd.html

Answer 2

/* 取得存在记录，同时创建临时表 */
SELECT id, '绿色' AS flg
  FROM tb2
 WHERE EXISTS (SELECT * FROM tb1
                WHERE tb1.fld1=tb2.fld1
                  AND tb1.fld2=tb2.fld2
                  AND tb1.fld3=tb2.fld3)
  INTO #t
 
/* 取得不存在记录 */
INSERT INTO #t
SELECT id, '红色' AS flg
  FROM tb2
 WHERE NOT EXISTS (SELECT * FROM tb1
                    WHERE tb1.fld1=tb2.fld1
                       OR tb1.fld2=tb2.fld2
                       OR tb1.fld3=tb2.fld3)
                       
/* 剩余为近似记录 */
INSERT INTO #t
SELECT id, '黄色' as flg
  FROM tb2
 WHERE NOT EXISTS (SELECT * FROM #t
                    WHERE #t.id=tb2.id)

Answer 3

你前贴的代码，就是UPDATE的代码对于数据少的情况下没问题，可是5万条以上的数据就跑不动了。

Answer 4

假定比较字段为 {fld1,fld2,fld3} 
假定 tb2 中用来记录比较结果的字段为 flg 
SQL code/* 默认为近似 */
UPDATE tb2 SET flg='黄色'/* 更新存在记录 */
UPDATE tb2 SET flg='绿色'
 WHERE EXISTS (SELECT * FROM tb1
                WHERE tb1.fld1=tb2.fld1
                  AND tb1.fld2=tb2.fld2
                  AND tb1.fld3=tb2.fld3)/* 更新不存在记录 */
UPDATE tb2 SET flg='红色'
 WHERE NOT EXISTS (SELECT * FROM tb1
                    WHERE tb1.fld1=tb2.fld1
                       OR tb1.fld2=tb2.fld2
                       OR tb1.fld3=tb2.fld3)这段代码如何优化。数据量大的时候跑不动了。

Answer 5

我用的是ACCESS数据库，怎么提升呀？

Answer 6

UPDATE tb2 SET flg='绿色' 
WHERE EXISTS (SELECT * FROM tb1 
                WHERE tb1.fld1=tb2.fld1 
                  AND tb1.fld2=tb2.fld2 
                  AND tb1.fld3=tb2.fld3)/* 更新不存在记录 */ 数据量大时，这条SQL语句速度最慢，就是它跑不出来，其它语句都可以。
数据量少时没有问题。

Answer 7

UPDATE tb2 SET flg='绿色' 
WHERE EXISTS (SELECT * FROM tb1 
                WHERE tb1.fld1=tb2.fld1 
                  AND tb1.fld2=tb2.fld2 
                  AND tb1.fld3=tb2.fld3)/* 更新不存在记录 */ 数据量大时，这条SQL语句速度最慢，就是它跑不出来，其它语句都可以。 
数据量少时没有问题。

Answer 8

给两个表加索引 (fld1,fld2,fld3)

Answer 9

没有EXISTS速度就快。
尤其是EXISTS 后面的条件如果是AND连接的话，速度就非常非常慢了。

Answer 10

SELECT DISTINCT tb2.* FROM tb2, tb1 WHERE tb2.fld1 = tb1.fld1 OR tb2.fld2 = tb1.fld2 OR tb2.fld3 =tb1.fld3

Answer 11

这个SQL不仅仅包含相似还包含完全相同的记录，不符合我的要求。

Answer 12

如果只统计的速度如何
SELECT COUNT(*) FROM tb2
 WHERE EXISTS (SELECT * FROM tb1  
                WHERE tb1.fld1=tb2.fld1 
                  AND tb1.fld2=tb2.fld2 
                  AND tb1.fld3=tb2.fld3)/

Answer 13

一个看似简单的问题，难倒了大多数人。现在只有
Tiger_Zhao
 
(VB老鸟) 的方法正确，就是记录上了一万以后特别特别慢。

Answer 14

我的实际应用代码如下：
update 光电子器件 set flg='绿色' where exists 
( select * 
  from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto
  where 光电子器件.Part_Name=opto.Part_Name 
  and 光电子器件.Part_Number=opto.Part_Number)

Answer 15

try:
update 光电子器件 a,(select distinct Part_Name,Part_Number from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto) b
set a.flg='绿色' 
where a.Part_Name=b.Part_Name and a.Part_Number=b.Part_Number

Answer 16

跨数据库，怪不得慢。最好先将比较相关的数据导进来
SELECT DISTINCT Part_Name,Part_Number
  FROM [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto
  ORDER BY Part_Name,Part_Number
  INTO #tb1
然后在在本数据库内操作
update 光电子器件 set flg='绿色' where exists 
( select * 
  from #tb1
  where 光电子器件.Part_Name=#tb1.Part_Name 
  and 光电子器件.Part_Number=#tb1.Part_Number)如果可能的话将 tb1 做成永久表，并且建上主键。

Answer 17

sql = "SELECT Part_Name,Part_Number from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto  into #tb1"
g_DB.Execute sql  执行这句代码时始终提示FROM子句语法错误，怎么回事？

Answer 18

ql = "SELECT Part_Name,Part_Number into tb1 from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto" 
g_DB.Execute sql  这样改过就可以了。

Answer 19

#tb1 换成 tb1。
再不行先创建好表，用 INSERT ... SELECT 方式代替 SELECT ... INTO

Answer 20

看来跟是否跨库没多大关系，最慢的就是那个EXISTS语句。

Answer 21

1、完全相同，表1、表2 Left join 联查结果不会包含NULL
2、完全不相同，表1、表2 LEFT join 联查结果不会同时半数的列是NULL
3、剩下的就是相似的。
 select *
from
(
select 
t1.f1 as field_s, 
t1.f2 as fiel_s,
t1.f3 as fie_s,
t2.f1,
t2.f2,
t2.f3
from 
table1 t1 left join table2 t2
on t1.f1 = t2.f1
and t1.f2 = t2.f2
and t1.f3 = t2.f3
) as a
where 
f1 is null or
f2 is null or
f3 is null用Left join联查表1、表2，取出结果中包含null的，再剔除三个同时为null的行就是结果了。剔除相同的你自己写吧。

Answer 22

新建一个 mdb，将参与比较的两张表（仅用于比较的两列）复制进去，再测试一下速度。
还有关闭杀毒软件试试。实在不行只能更换数据库。顺便问一句：mdb 有多大？

Answer 23

修正：1、完全相同，表1、表2 Left join 联查结果不包含NULL 
2、完全不相同，表1、表2 LEFT join 联查结果半数的列是NULL 

Answer 24

杀毒软件全部已经关闭。
MDB文件15M字节。

Answer 25

那为什么下面的代码速度就非常快：
/* 更新不存在记录 */ 
UPDATE tb2 SET flg='红色' 
WHERE NOT EXISTS (SELECT * FROM tb1 
                    WHERE tb1.fld1=tb2.fld1 
                      OR tb1.fld2=tb2.fld2 
                      OR tb1.fld3=tb2.fld3) 

Answer 26

同样的语句，不同数据库执行性能不一样。28楼的方法性能怎么样？
加不加 DISTINCT 都试一下

Answer 27

28楼的运行时提示UPDATE语法有错，不知道怎么回事？

Answer 28

DISTINCT 和ORDER BY 都试过了，没有改善。

Answer 29

Access 一定要用 JOIN 
update 光电子器件 a
  INNER JOIN  (select distinct Part_Name,Part_Number from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto) b
     ON a.Part_Name=b.Part_Name and a.Part_Number=b.Part_Number
set a.flg='绿色' 

Answer 30

仍然提示UPDATE 处存在语法错误。

Answer 31

我用下面的方式语法是正确的
UPDATE 学生表 a INNER JOIN 成绩表 b ON a.学号 = b.学号 SET b.成绩 = 0
WHERE (((a.姓名)="张三"));

Answer 32

要修改成下面的代码才可以：
update 光电子器件 a
  INNER JOIN  (select distinct Part_Name,Part_Number into b from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto)
     ON a.Part_Name=b.Part_Name and a.Part_Number=b.Part_Number
set a.flg='绿色' 

Answer 33

问题终于解决了，现在5万条记录只需不到一分钟就处理完了，代码如下：
update 光电子器件 a 
  INNER JOIN  (select distinct Part_Name,Part_Number into b from [D:\PtfExcelApp20100207\database\PartTableDB.mdb;pwd=123].opto) 
    ON a.Part_Name=b.Part_Name and a.Part_Number=b.Part_Number 
set a.flg='绿色' 

Answer 34

数据库不同，执行效率只能拿等价的不同 SQL 去试。
实践是检验真理的唯一标准。

Answer 35

查找不同记录哪错了？
update 电阻器 a LEFT JOIN [D:\company_lib\PartTableDB.mdb;pwd=123].resistor1 b ON a.Part_Number=b.Part_Number and a.Material_code_13= b.Material_code_13 set a.flg='红色' where b.Part_Number is null and b.Material_code_13 is null 详见帖子：http://topic.csdn.net/u/20100419/21/5f538cc0-d532-4c1b-acfd-efe775d4c7a4.html
再问如何查找不匹配数据！期待老鸟！双贴200分奉送！[问题点数:100分]

Answer 36

数据库：ACCESS
有如下查询：
update 电阻器 a LEFT JOIN [D:\company_lib\PartTableDB.mdb;pwd=123].resistor1 b ON a.Part_Number=b.Part_Number and a.Material_code_13= b.Material_code_13 set a.flg='红色' where b.Part_Number is null and b.Material_code_13 is null  目的是查找用户数据库中“电阻器”表与公司数据库“resistor1”表之间的不同记录。用两表的Part_Number字段和Material_code_13字段进行匹配，如果两个字段都不同，则认为是缺少元器件，并标记为“红色”。
为什么我上面的查询b.Part_Number is null and b.Material_code_13 is null 中的and不起作用，把其中一个匹配字段不同，另一个匹配字段相同的记录也给查出来了？为什么我上面的查询b.Part_Number is null and b.Material_code_13 is null 中的and不起作用，把其中一个匹配字段不同，另一个匹配字段相同的记录也给查出来了？
也就是使用：
b.Part_Number is null and b.Material_code_13 is null  
和
b.Part_Number is null or b.Material_code_13 is null
查询出的结果竟然相同！很是郁闷！！