Excel 2016 VBA连接PostgreSQL，两同结构表取最新数据

你可以通过先合并两张表的原始数据，再进行聚合计算的方式实现需求，具体修改是把原查询中的单表数据源替换为table1和table2的UNION ALL结果，再执行后续的分组、取最大值和字段解析逻辑。修改后的VBA代码如下：

Const sqlconnection = "Provider=visoledb;"
Dim conn As New Connection
conn.ConnectionString = sqlconnection

conn.Open
Dim rs As Recordset

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FINDDATA"
Sheets("FINDDATA").Select
ActiveSheet.Range("A1").Select
    
    v = "SELECT DISTINCT latest.id, " _
        & "SUBSTRING(latestRECORD,1,10) date, " _
        & "SUBSTRING(latestRECORD,12,7) code, " _
        & "SUBSTRING(latestRECORD,20,80) term " _
        & "FROM ( SELECT id, MAX(CAST(date AS VARCHAR(10)) + '|' + RIGHT(code,7) + '|' + RIGHT(term,80)) as latestRECORD " _
                    & "FROM (SELECT id, date, code, term FROM table1 WHERE id IN (2,3,6,7,14,22,29,54) AND code LIKE '22K' " _
                        & "UNION ALL " _
                        & "SELECT id, date, code, term FROM table2 WHERE id IN (2,3,6,7,14,22,29,54) AND code LIKE '22K') AS combined " _
                & "GROUP BY id ) AS latest " _
        & "ORDER BY id "
Set rs = conn.Execute(v)
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
    .Refresh
End With
conn.Close

修改说明： #

• 新增子查询combined，通过UNION ALL将两张表中符合筛选条件的数据合并，确保原始数据完整保留
• 后续分组、取最大值逻辑基于合并后的combined表执行，能获取到两个表中每个id的最新记录
• 完全保留了你原有的SUBSTRING字段解析逻辑，符合不能使用LIMIT的限制

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