示例下载链接:
链接:https://pan.baidu.com/s/16gG-y4tVndGUGgSWlbUmkg
今天题材如下:
这是肉联厂最近一个月的产品报价单(有小伙伴给小编留言,怎么例子都是猪肉。。。下次用海鲜吧)
现需要获取各厂家产品的最新报价
分析题材,就是每个厂家产品报价的最大日期对应的单价,也就是多条件查询
VBA代码如下:
Sub t()
Dim aData, aRes, Dic
Dim x&, strID$, r&, c&
Set Dic = CreateObject('Scripting.Dictionary') '后期绑定字典
aData = Range('A1').CurrentRegion '数据来源
ReDim aRes(1 To UBound(aData), 1 To UBound(aData, 2)) '结果数组大小
For x = 2 To UBound(aData) '遍历数据源
strID = aData(x, 1) & aData(x, 3) '连接字符作为单一条件
If Not Dic.exists(strID) Then '不存在字典中
r = r + 1 '累加数据行
Dic(strID) = r '写入字典
aRes(r, 1) = aData(x, 1): aRes(r, 2) = aData(x, 2) '写入内容
aRes(r, 3) = aData(x, 3): aRes(r, 4) = aData(x, 4)
Else
c = Dic(strID) '提取所在结果行位置
If aRes(c, 3) < aData(x, 3) Then '判断日期大小
aRes(c, 3) = aData(x, 3): aRes(c, 4) = aData(x, 4) '写入
End If
End If
Next
Range('G11').Resize(r, 4) = aRes '输出
Set Dic = Nothing ' 释放
End Sub
在前面的练习的题材中,小编有提到字典具有极佳的检索功能,在多个条件查询时,将其整理为一个字符串
在这题中,就是用了该技巧,strID将厂家以及产品连接成为一个字符串作为关键字写入字典中
之后提取每个关键字对应的行位置,在进行日期大小的比较写入对应的信息既可
SQL代码如下:
Sub t1()
Dim Conn, Rec, aField, intx&
Dim strSQL$, strSource$
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 = '[Sheet1$A1:D' & Cells(Rows.Count, 4).End(3).Row & ']' '数据来源区域
strSQL = 'Select * ' _
& 'From ' & strSource _
& 'Where 厂家&日期&产品 in (' _
& 'Select 厂家&最近日期&产品 ' _
& 'From (' _
& 'Select 厂家, Max(日期) As 最近日期 ,产品 ' _
& 'From ' & strSource _
& ' Group By 厂家,产品))' _
& 'Order By 厂家,日期 Desc'
Set Rec = Conn.Execute(strSQL) '执行SQL
'Fields包含了所有字段的,Fields.Count 得到字段的数量
ReDim aField(1 To Rec.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('G1').Resize(, UBound(aField)) = aField '写入字段
'使用单元格CopyFromRecordset方法将Rec记录写入到指定单元格
Range('G2').CopyFromRecordset Rec
Conn.Close '关闭连接
Set Conn = Nothing '释放
Set Rec = Nothing '释放
End Sub
SQL语句
Select *
From [Sheet1$A1:D22]Where 厂家&日期&产品 in (
Select 厂家&最近日期&产品
From (
Select 厂家, Max(日期) As 最近日期 ,产品
From [Sheet1$A1:D22]
Group By 厂家,产品))
Order By 厂家,日期 Desc
执行后返回的内容
首先,子查询先获取每个厂家对应的产品最新的日期
Select 厂家, Max(日期) As 最近日期 ,产品 From [Sheet1$A1:D22] Group By 厂家,产品
返回以下结果
跟着将获取的内容使用 & 连接起来作为一个单一的条件
Select 厂家&最近日期&产品
From (
Select 厂家, Max(日期) As 最近日期 ,产品
From [Sheet1$A1:D22]
Group By 厂家,产品
)
返回以下结果
最后在连接起来 使用 条件查询的 IN 运算获取集合中的数据
联系客服