向大师们请教，对'|A|' Like '%|' + [Items] + '|%'VBA ADO SQL模糊查询的疑惑

cui26896

问题源自该帖“Excel VBA 在可变动替代SUMIFS进行统计[较大数据量情况下”，

http://club.excelhome.net/thread-1512668-1-1.html

。在帖中

microyip

老师给出的自定义函数公式解决方案中，出现这样一种独特的自己第一次见到的模糊查询方法，Select  Sum([Amount]) As SumAmount From [db$] Where True And [Year]=2016 And [Month]<=10 And '|01|02|' Like '%|' + [Company] + '|%' And '|A|B|' Like '%|' + [Items] + '|%'，用任意分隔符将查询字符串分开，反向对字段的匹配实施like查询。这是一种什么方法，在论坛中没有任何一个帖中出现过，所以，只有通过本实例来试着看了。下面是写的简化后的测试代码

1. Sub mytest()
   
2. Dim cnn As Object, sql, k, t, arr
   
3. Set cnn = CreateObject("ADODB.Connection")
   
4. cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
5. 'cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
6. Sheets("R").Activate
   
7. arr = cnn.Execute("SELECT DISTINCT Year FROM [db$]").GetRows
   
8. 'Debug.Print Sheets(2).Name
   
9. Sheets(2).[b20].Resize(1, 3) = arr
   
10. Sheets(2).[b27].Resize(1, 3) = arr
    
11. 'sql = "Select  Sum([Amount]) From [db$] Where True And [Month]<=10 And '@01@02@' Like '%@' + [Company] + '@%' And '|A|' Like '%|' + [Items] + '|%' group by year"
    
12. 'arr = cnn.Execute(sql).GetRows
    
13. 'Sheets(2).[a35].Resize(1, 3) = arr
    
14. 'sql = "Select  Sum([Amount]) From [db$] Where True And [year]=2016  And [Month]<=10 And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
    
15. sql = "Select  Sum([Amount]) From [db$] Where True And '@2016@' Like '%@' + [Year] + '@%' And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
    
16. arr = cnn.Execute(sql).GetRows
    
17. Sheets(2).[a37].Resize(1, 3) = arr
    
18. cnn.Close
    
19. Set cnn = Nothing
    
20. End Sub

复制代码

sql = "Select  Sum([Amount]) From [db$] Where True And [Month]<=10 And '@01@02@' Like '%@' + [Company] + '@%' And '|A|' Like '%|' + [Items] + '|%' group by year
sql = "Select  Sum([Amount]) From [db$] Where True And [year]=2016  And [Month]<=10 And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
sql = "Select  Sum([Amount]) From [db$] Where True And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
上面三种方案都通过了测试，但是
sql = "Select  Sum([Amount]) From [db$] Where True And '@2016@' Like '%@' + [Year] + '@%' And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
却无法通过，为什么在其他列都能通过，换到year这一列就无法通过呢？所用的这种like方法，是透视表中的还是access的，还是VBA中的什么方法。
Test(by.micro).xlsm.zip (28.17 KB, 下载次数: 1)

2019-12-18 08:20 上传

点击文件名下载附件

microyip

先撇开实际案例说明一下Like吧
正常情况下，大家使用时，是不是都是下面这样检查的？
想看看已知字符串“ABCDEFG”里面是否包含“EF”，然后写出的代码是

1. sStr="ABCEDEFG"
   
2. If sStr Like "*EF*" Then
   
3.   Msgbox "Yes"
   
4. Else
   
5.   Msgbox "No"
   
6. End If

复制代码

如果我反过来看呢？查找未知字符串里面在不在某些字符串里面呢？我用分隔符把某些字符串连接起来，用分隔符作为界线，是不是也可以呢？

1. Redim vData(1 To 3)
   
2. vData(1)="BC"
   
3. vData(2)="DE"
   
4. vData(3)="123ABCDE567"
   
5. 'vData是数据表内的具体数据
   
6. For nI=1 To Ubound(vData)
   
7.   If "|ABCD|EF|" Like "*|" & vData(nI) & "|*" Then
   
8. '"|ABCD|EF|"是把查找指定条件进行罗列
   
9.     Msgbox "第" & nI & "个字符串“" & vData(nI) & "”在指定范围！"
   
10.   Else
    
11.     Msgbox "第" & nI & "个字符串“" & vData(nI) & "”不在指定范围！"
    
12.   End If
    
13. Next

复制代码

着重说明，为了保证分隔清晰，罗列条件的前后必须带有分隔符！相当于对于每个vData(nI)进行分别对“ABCD”、“EF”进行一次检查，核对是否符合条件。注意：第三个，分清楚谁是谁的一部分！

microyip

灵活变通嘛，关键是看看谁是谁的其中一部分，然后在Like的前面放“全部”，Like的后面放“部分”。这个操作并不是“透视表中的还是access的，还是VBA中的什么方法”任何一样东西，是个人在实际使用中的一种变通应用。

microyip

至于你给出的语句中，应用原理是没错的，但忘记了Like是应用在字符串的，不是应用在数值的。把[Year]强制换成字符串后的结果呢？

1. sql = "Select  Sum([Amount]) From [db$] Where True And '@2016@' Like '%@' + Trim([Year]) + '@%' And '@01@02@' Like '%@' + [Company] + '@%' And '|A|B|' Like '%|' + [Items] + '|%'"
   

复制代码

cui26896

谢谢老师的帮助！再向老师请教一下，这种由字符串反查字段的方法，是透视表，还是Access中引伸出来的方法,还是VBA本来就有这种方法？

cui26896

刚才，没有看到老师已经做了答复，谢谢老师！！！

microyip

生活不能一成不变、墨守成规，任何东西都可能存在变通理解、变通处理、变通做法！只要你敢想敢做，就可能生活更精彩！

cui26896

microyip 发表于 2019-12-18 08:48
先撇开实际案例说明一下Like吧
正常情况下，大家使用时，是不是都是下面这样检查的？
想看看已知字符串“ ...

感谢老师耐心细致的解答！！！