用Sql 语句对比两表数据差异

汶水苍柏

请教各位老师一个问题，以下代码：

根据学生编号、姓名、性别、籍贯、年级对比两表的数据差异，现在
1、能把数据表中有编号，数据有误的列出来，如 100007的姓名字段有误；
2、能把数据表中无编号的，列出来，如100195
现在想完善的功能：
1、能把数据表中有编号，数据有误的列出来，如 100007；怎么能把有误的字段给提取或标识出来
2、怎么能把能把数据表中无编号的，编号标识出来

能做到吗，感谢

Sub 查找没有的编码()
Set cnn = CreateObject("ADODB.connection")
Set rst = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
Sql = " select a.学生编号,a.姓名,a.性别,a.籍贯,a.年级  from [录入表$] as a left join [数据$] as b on a.学生编号 = b.学生编号 and a.姓名=b.姓名 and a.性别=b.性别 and a.籍贯=b.籍贯 and a.年级=b.年级 where b.学生编号 is null"
kk = ""
rst.Open Sql, cnn, 1, 3
If Not rst.EOF Then
  
    For i = 1 To rst.RecordCount
    kk = kk & "；" & rst.Fields("学生编号")
    rst.MoveNext
    Next
  
  MsgBox "以下编号的数据有误或无，请查证后录入 " & kk
End If
  
Range("h2:v50000") = ""
Range("h2").CopyFromRecordset cnn.Execute(Sql)
cnn.Close
Set cnn = Nothing
End Sub

汶水苍柏

如 100007,姓名字段数据错误，谢谢

sdytsxd991122

汶水苍柏

感谢帮助，这个精确取错误数据字段的问题SQL语句是有点难，哪位老师能帮忙写一下呢，谢谢

WANT-T

你这个需要SQL嵌套一下；
1、用内链接INNER JOIN 找到左右都有的；
2、查询左表，排除1里左右都有的，两个方法，编号 NOT IN , 或者LEFT JOIN WHERE 右 IS NULL；
写的比较简单，不知道能能否看懂：
SQL代码如如下：

1. 
   
2. Sql = " select a.学生编号,a.姓名,a.性别,a.籍贯,a.年级 " & _
   
3.     "from [录入表$] a " & _
   
4.     "INNER Join [数据$] b " & _
   
5.         "on a.学生编号 = b.学生编号 " & _
   
6.         "and a.姓名=b.姓名 " & _
   
7.         "and a.性别=b.性别 " & _
   
8.         "and a.籍贯=b.籍贯 " & _
   
9.         "and a.年级=b.年级"
   
10.         
    
11. Sql = "SELECT A.* FROM [录入表$] A " & _
    
12.         "LEFT JOIN (" & Sql & ") B ON A.学生编号=B.学生编号 " & _
    
13.         "WHERE B.学生编号 IS NULL"
    

复制代码

亲测有效；
祝工作顺利，天天开心，好人一生平安！

汶水苍柏

WANT-T 发表于 2023-3-29 15:12
你这个需要SQL嵌套一下；
1、用内链接INNER JOIN 找到左右都有的；
2、查询左表，排除1里左右都有的，两 ...

感谢老师帮助，您的代码和我写的现在的功能一致，现在把有错误的编号数据及没有的编号都找出来了，不过我想实现的是，这条错误数据是哪个字段错误的，怎么找出来，如100007        a        女        广西        1年级，这条数据错误，是姓名字段错误，那怎么找出姓名这个字段呢，谢谢

zpy2

汶水苍柏 发表于 2023-3-29 15:25
感谢老师帮助，您的代码和我写的现在的功能一致，现在把有错误的编号数据及没有的编号都找出来了，不过我 ...

select * from 数据 limit 3;
cli_one_dim~数据~1;
select * from 数据union limit 5;
cli_one_dim~录入表~1;
create temp table aa as
select a.* from 录入表union a left join 数据union b using(属性,数量) where b.学生编号 is null and a.属性 not like '%__EMPTY%';
select * from aa;
cli_create_two_dim~aa~属性~数量;
select * from aa_two_dim;

转成一维再匹配？

WANT-T

zpy2 发表于 2023-3-29 16:38
select * from 数据 limit 3;
cli_one_dim~数据~1;
select * from 数据union limit 5;

想要比对出是哪个字段有问题，得转成纵表或者一位表才好处理，或者用数组循环加字典，结合条件格式；用sql直接比较很麻烦，结果表再left join 数据表，把所有的字段都拉出来，一个一个对比，打标，输出到表格里，使用条件格式来标记颜色！工作量不小！
类似这样


代码：

1. Sub 查找没有的编码()
   
2. 
   
3. Set Cnn = CreateObject("ADODB.connection")
   
4. Set rst = CreateObject("ADODB.Recordset")
   
5. 
   
6. 'Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
7. Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
   
8. Sql = " select a.学生编号,a.姓名,a.性别,a.籍贯,a.年级 " & _
   
9.     "from [录入表$A1:E] a " & _
   
10.     "INNER Join [数据$] b " & _
    
11.         "on a.学生编号 = b.学生编号 " & _
    
12.         "and a.姓名=b.姓名 " & _
    
13.         "and a.性别=b.性别 " & _
    
14.         "and a.籍贯=b.籍贯 " & _
    
15.         "and a.年级=b.年级"
    
16.         
    
17. Sql1 = "SELECT a.学生编号,a.姓名,a.性别,a.籍贯,a.年级 FROM [录入表$] A " & _
    
18.         "LEFT JOIN (" & Sql & ") B ON A.学生编号=B.学生编号 " & _
    
19.         "WHERE B.学生编号 IS NULL"
    
20.         
    
21. Sql2 = "SELECT A.*," & _
    
22.         "IIF(A.姓名=B.姓名,1,0) AS IS_SAME_1," & _
    
23.         "IIF(A.性别=B.性别,1,0) AS IS_SAME_2," & _
    
24.         "IIF(A.籍贯=B.籍贯,1,0) AS IS_SAME_3," & _
    
25.         "IIF(A.年级=B.年级,1,0) AS IS_SAME_4 " & _
    
26.     "FROM (" & Sql1 & ") A LEFT JOIN [数据$] B ON A.学生编号=B.学生编号"
    
27. With Range("H2")
    
28.     .CurrentRegion.Offset(1).Clear '清除数据
    
29.     .CopyFromRecordset Cnn.Execute(Sql2)
    
30.     With .Resize(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count - 4)
    
31.         .Select
    
32.         With .FormatConditions
    
33.             .Delete '清楚条件格式
    
34.             With .Add(Type:=xlExpression, Formula1:="=RC[4]=0")   '添加条件格式
    
35.                 .Interior.Color = 255
    
36.             End With
    
37.         End With
    
38.     End With
    
39. End With
    
40. 
    
41. 
    
42. Cnn.Close
    
43. Set Cnn = Nothing
    
44. 
    
45. 
    
46. End Sub

复制代码

汶水苍柏

感谢两位老师帮助，学习一下

汶水苍柏

Sql = " select a.学生编号,a.姓名,a.性别,a.籍贯,a.年级  from [录入表$] as a left join [数据$] as b on a.学生编号 = b.学生编号 and a.姓名=b.姓名 and a.性别=b.性别 and a.籍贯=b.籍贯 and a.年级=b.年级 where b.学生编号 is null"
arr = Array("姓名", "性别", "籍贯", "年级")
For X = 0 To UBound(arr)
   kk = ""
    Sql2 = " select a.学生编号,a.姓名,a.性别,a.籍贯,a.年级  from (" & Sql & ") as a left join [数据$] as b on a.学生编号 = b.学生编号 and a." & arr(X) & "=b." & arr(X) & " where b.学生编号 is null"
    rst.Open Sql2, cnn, 1, 3
    If Not rst.EOF Then
        For ii = 1 To rst.RecordCount
        kk = kk & "；" & rst.Fields("学生编号") & arr(X)
        rst.MoveNext
        Next
        MsgBox "无学生编号" & kk & "的数据错误，请查证后录入"
    End If
  rst.Close
Next
先查询出，没有编号或有编号字段错的条目
再用循环分别查询 "姓名", "性别", "籍贯", "年级"这四个字段，如果空值则本字段错误，但是以上循环写法不对，哪里错了呢？SQL语句能放在循环内吗？请教