如何使用含CASE WHEN的VBA SQL脚本更新Excel表格？

嘿，我看你想通过带CASE WHEN的SQL来更新Excel里的users表，正好帮你梳理下问题，再给你一个能正常运行的方案。

你的工作表数据 #

先明确下你提供的两个表的结构和数据：

users表 #

affiliations表（注意你拼写错成affiliatons啦） #

修正你的SQL语句 #

你原来写的Excel SQL有几个小问题：比如当COUNT等于1时，直接引用[affiliatons$].[new_club]会报错——因为计数子查询只返回数字，没法直接关联到具体的new_club值；还有字符串转义的"要换成普通双引号。

结合你的需求（无关联记录用原club，1条关联用对应new_club，多条则设为undecided），修正后的Excel SQL如下：

UPDATE [users$] 
SET [fanatic_of] = 
    CASE 
        WHEN (SELECT COUNT(*) FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id]) = 0 
            THEN [users$].[club]
        WHEN (SELECT COUNT(*) FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id]) = 1 
            THEN (SELECT TOP 1 [new_club] FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id])
        ELSE "undecided"
    END

对应的标准SQL版本：

UPDATE users 
SET fanatic_of = 
    CASE 
        WHEN (SELECT COUNT(*) FROM affiliations WHERE affiliations.user_id = users.user_id) = 0 
            THEN users.club
        WHEN (SELECT COUNT(*) FROM affiliations WHERE affiliations.user_id = users.user_id) = 1 
            THEN (SELECT new_club FROM affiliations WHERE affiliations.user_id = users.user_id)
        ELSE 'undecided'
    END

在VBA中执行更新脚本 #

如果要在VBA里运行这个SQL，你可以用ADODB连接来操作Excel数据源，代码示例如下：

Sub UpdateUsersFanaticOf()
    Dim conn As Object
    Dim sqlStr As String
    
    ' 创建ADODB连接对象
    Set conn = CreateObject("ADODB.Connection")
    
    ' 设置连接字符串（适配.xlsx格式，旧版.xls请换Provider和Extended Properties）
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"""
    
    ' 拼接SQL语句
    sqlStr = "UPDATE [users$] " & _
             "SET [fanatic_of] = " & _
             "    CASE " & _
             "        WHEN (SELECT COUNT(*) FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id]) = 0 " & _
             "            THEN [users$].[club] " & _
             "        WHEN (SELECT COUNT(*) FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id]) = 1 " & _
             "            THEN (SELECT TOP 1 [new_club] FROM [affiliations$] WHERE [affiliations$].[user_id] = [users$].[user_id]) " & _
             "        ELSE ""undecided"" " & _
             "    END"
    
    ' 执行更新
    conn.Execute sqlStr
    
    ' 关闭连接并释放对象
    conn.Close
    Set conn = Nothing
    
    MsgBox "更新完成！", vbInformation
End Sub

注意事项 #

• 确保affiliations表的拼写正确（你原来的写法少了一个字母i）
• 如果你用的是旧版.xls文件，连接字符串要改成：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
• HDR=YES表示表的第一行是表头，若你的表没有表头，改成HDR=NO，字段名要用F1、F2这样的格式

备注：内容来源于stack exchange，提问作者Jose Cabrera Zuniga