Excel VBA中SQL父子查询异常：NAME5误出结果集问题排查

我用Excel VBA编写SQL查询，目标是从INPUT表筛选出无对应子项的行到OUTPUT结果集，目前仅聚焦5H和6H列：

• UNION前的语句：筛选1H至6H列均非空的行，这部分逻辑正常
• UNION后的语句：本应筛选「5H非空、6H为空且无对应子项」的行，但NAME5（NAME6的父项）错误出现在结果中，而NAME16（NAME17的父项）却被正确排除，需排查问题原因。

原VBA SQL代码 #

SQLQuery = _  
"SELECT  v1.[name], v1.[1H], v1.[2H], v1.[3H], v1.[4H], v1.[5H], v1.[6H]" & _
    " FROM [INPUT$] as v1 WHERE  v1.[2H] IS NOT NULL and " & _
    " v1.[3H] IS NOT NULL AND v1.[4H] IS NOT NULL AND v1.[5H] IS NOT NULL AND v1.[6H] IS NOT NULL " & _
"UNION " & _
"SELECT DISTINCT  v1.[name], v1.[1H], v1.[2H], v1.[3H], v1.[4H], v1.[5H], v1.[6H]" & _
    " FROM [INPUT$] as v1  " & _
    " INNER JOIN  " & _
    " (SELECT  * " & _
        " FROM [INPUT$] WHERE [5H] IS NOT NULL AND  [6H] IS NULL )  as v2  " & _
        " ON v1.[1H] = v2.[1H] and v1.[2H] = v2.[2H] and v1.[3H] = v2.[3H] and v1.[4H] = v2.[4H]  " & _
        " WHERE v1.[5H] <> v2.[5H]  "

问题原因分析 #

你UNION后的语句逻辑完全搞反了——当前逻辑是筛选存在同层级其他父项的行，而非无对应子项的行：

1. 子查询v2筛选的是「5H非空、6H为空」的行（即你定义的父项候选）
2. INNER JOIN关联v1和v2，要求前4H列完全匹配，但5H列不相等
3. 这意味着只要当前v1行存在另一个同前4H、不同5H的父项候选行，就会被选中。NAME5刚好符合这个条件（存在其他同层级父项），所以被错误保留；NAME16没有同层级其他父项，因此被排除，和你的需求完全相反。

修正方案 #

要筛选「5H非空、6H为空且无对应子项」的行，应该用不存在性检查（NOT EXISTS），确保当前行的前5H列没有对应的子项（即不存在前5H匹配、6H非空的行）。修改后的UNION后语句如下：

SQLQuery = _  
"SELECT  v1.[name], v1.[1H], v1.[2H], v1.[3H], v1.[4H], v1.[5H], v1.[6H]" & _
    " FROM [INPUT$] as v1 WHERE  v1.[2H] IS NOT NULL and " & _
    " v1.[3H] IS NOT NULL AND v1.[4H] IS NOT NULL AND v1.[5H] IS NOT NULL AND v1.[6H] IS NOT NULL " & _
"UNION " & _
"SELECT  v1.[name], v1.[1H], v1.[2H], v1.[3H], v1.[4H], v1.[5H], v1.[6H]" & _
    " FROM [INPUT$] as v1  " & _
    " WHERE v1.[5H] IS NOT NULL AND v1.[6H] IS NULL " & _
    " AND NOT EXISTS (" & _
        " SELECT 1 FROM [INPUT$] as v2 " & _
        " WHERE v2.[1H] = v1.[1H] AND v2.[2H] = v1.[2H] " & _
        " AND v2.[3H] = v1.[3H] AND v2.[4H] = v1.[4H] " & _
        " AND v2.[5H] = v1.[5H] AND v2.[6H] IS NOT NULL " & _
    " )"

修正后的逻辑：

1. 先筛选出「5H非空、6H为空」的父项候选行
2. 再通过NOT EXISTS确保这些行没有对应的子项（即不存在前5H完全匹配、6H非空的行）
3. 不需要DISTINCT，因为NOT EXISTS不会产生重复行

内容的提问来源于stack exchange，提问作者Peter