SQL 模糊查询

Excel VBA练习 2021-06-09

918

最近我市开展全民核酸检测,做为好市民的小编,积极响应国家的号召,排了2个小时的队终于做完了

在排队无聊等待中,突然想起今天工作筛选工作表的一个小问题

昨天写了下条件查询,那今天就写个模糊查询吧

我们知道在Excel函数查询时,有两个比较重要的通配符(*)和(?),星号代表0个或者多个字符,问号代表1个字符

而在SQL In Excel中也有两个通配符,一个是%(百分号),另外一个是(_)下划线

如下图所示

数据纯属虚拟,如有雷同,那就呵呵

这是某公司工作人员的人员明细,有一百多人

现如今,需要查询刘姓的工作人员

使用函数

看不全左右拖动

=IFERROR(INDEX($A$2:$G$173,SMALL(IF(ISNUMBER(FIND("刘",$B$2:$B$173))*ROW($1:$172),ROW($1:$172),200),ROW(A1)),COLUMN(A1)),"")

使用VBA

看不全左右拖动

Sub Like_StrName()
    Dim aData, aRes
    Dim intx&, inty&, iRow&, strName$
    aData = Range("a1").CurrentRegion
    ReDim aRes(1 To UBound(aData), 1 To UBound(aData, 2))
    iRow = 1
    For intx = 2 To UBound(aData)
        strName = aData(intx, 2) '姓名
        If strName Like "刘*" Then '判断是否刘姓
            iRow = iRow + 1
            For inty = 1 To UBound(aData, 2) '遍历写入内容
                aRes(iRow, inty) = aData(intx, inty)
            Next
        End If
    Next
    For inty = 1 To UBound(aData, 2) '写入表头
        aRes(1, inty) = aData(1, inty)
    Next
    With Range("K10").Resize(iRow, UBound(aRes, 2))
        .Value = aRes '输出内容
        .Borders.ColorIndex = 23 '边框
        .Columns.AutoFit '自适应列宽
    End With
End Sub

运行结果

循环判断是否包含刘姓员工,成立写入结果数组

当然,这里也可以使用Instr或者Range.Find方法,这里不在述说,等写到VBA在慢慢唠嗑

使用SQL

等号运算符用于精确匹配。而本例中要找出所有“刘”姓的员工，由于无法得知“刘”姓员工的名字，而且“刘”姓员工的名字都不一致，因而无法通过等号运算进行查询，此时需要用到模糊查询
在SQL查询语句中使用通配符,必须借助Like运算符

其语法

标识包含要计算的数据的字段 Like 匹配模式

运用LIKE进行模糊匹配

1.查询刘姓的工作人员

strSQL = "Select * From " & strSource & " Where 姓名 Like '刘%'"

2.查询2个字姓名的工作人员

strSQL = "Select * From " & strSource & " Where 姓名 Like '__'"

3.查询员工编码为1开头8结尾的信息

strSQL = "Select * From " & strSource & " Where 员工编号 Like '1%8'"

4.查询员工姓名刘姓或者姓名以平结尾的信息

strSQL = "Select * From " & strSource & " Where 姓名 Like '刘%' Or 姓名 Like '%平'"

5.查询指定员工编号第4位和第5位的员工信息

strSQL = "Select * From " & strSource & " Where 员工编号 Like '___11%'"

这里员工编号 Like '_ _ _ 11%'"前3个使用了_,每一个_代表任意一个字符

再来个荔枝,什么都不多,我什么都不多,荔枝挺多的~

以上是一份不知名大企业的物料明细

现需要查询商品代号包含数字的物料名称

模糊匹配中字符区间的表示

6.查询包含数字的商品代号

strSQL = "Select * From " & strSource & " Where 商品代号 Like '%[0-9]%'"

7.查询中间包含3或4,后面跟5或者7的商品代号

strSQL = "Select * From " & strSource & " Where 商品代号 Like '%[3-4][5-7]%'"

8.查询评分A-C的物料信息

strSQL = "Select * From " & strSource & " Where 评分 Like '[A-C]'"

9.查询评分非A-C的物料信息

strSQL = "Select * From " & strSource & " Where 评分 Like '[!A-C]'"

模糊匹配中特殊字符的表示

在LIKE运算符的匹配模式中，一些特殊的字符在LIKE运算中有特殊的含义，如：方括号（[]）、百分号（%）、下画线（_）等。当字段值中包含这些字符时，就必须使用方括号将这些字符括起来，以表示这些字符

10.查询型号包含[]的型号

strSQL = "Select * From " & strSource & " Where 型号 Like '[[]%[]]'"

11.查询型号不包含[]的型号

strSQL = "Select * From " & strSource & " Where 型号 Not Like '[[]%[]]'"

呃,差不多了,剩下的在慢慢研究下

SQL完整代码如下

Sub SQL_Like()
    Dim Conn As Object, Rec As Object
    Dim aField, aData, intx As Long, strSQL As String
    Dim strSource As String
    Set Conn = CreateObject("Adodb.Connection")
    If Application.Version < 12 Then '判断Excel的版本号,以使用不同的连接字符串
        Conn.Open "Provider=Microsoft.ACE.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullName
    Else
        Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullName
    End If
    strSource = "[Sheet2$A1:D]" '数据来源区域
    
    strSQL = "Select * From " & strSource & " Where 型号 Not Like '[[]%[]]'"
    
    Set Rec = Conn.Execute(strSQL)
    ReDim aField(1 To Rec.Fields.Count) 'Fields包含了所有字段的,Fields.Count 得到字段的数量
    For intx = 0 To Rec.Fields.Count - 1 'Fields.Count的下标为0,因此总数-1,字段数组从1开始,所以每次都需要+1
        aField(intx + 1) = Rec.Fields(intx).Name
    Next
    Range("J1").Resize(, UBound(aField)) = aField '写入字段
    Range("J2").CopyFromRecordset Rec '使用单元格CopyFromRecordset方法将Rec记录写入到指定单元格
    Conn.Close '关闭连接
    Set Conn = Nothing '释放
    Set Rec = Nothing '释放
End Sub

SQL其他都是固定的,只需要设置SQL语句即可

收工!!下期见

数据库

文章转载自Excel VBA练习，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL 模糊查询

评论