ado 使用full join 查询。

hyz00001

hyz00001 发表于 2019-1-23 16:21
谢谢您。

上午的那个已经搞定。主要是括号使用了中文状态的括号。

三个表的连接也已经搞定，但只是内连接 意思是三张表都要有数据才显示。在这里的需要是：

已经由三张表组成了编码表:bm，只要bm表里面有数的都显示出来。

    s1 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期初清单$]"
    s2 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期末清单$]"
    bm1 = "select 资产编码 from [期初清单$] union all select 资产编码 from [期末清单$] union all select 资产编码 from [明细账$]"
    bm = "select distinct 资产编码 from (" & bm1 & ")"

s = "select * from (" & bm & ") t1,(" & s1 & ") t2,(" & s2 & ") t3 where t1.资产编码=t2.资产编码 and t1.资产编码=t3.资产编码"

05050818

amazeyeli 发表于 2019-1-23 15:15
select * from t1 full join t2 on
=
select * from t1 left join t2 on

真心没看懂，能解释一下吗

hyz00001

hyz00001 发表于 2019-1-23 16:21
谢谢您。

上午的那个已经搞定。主要是括号使用了中文状态的括号。

感谢各位，刚才那个问题我自己想明白了。可以通过多重连接来实现三张表left join查询。

但另外两个问题困扰我，请帮忙看看。
问题1:我查询之后的表头怎么样弄到。
问题2:如果我的excel表里面的字段比较奇怪，要怎么样编写语句。例如字段名为：“原值(综合本位币):余额”

自己修改之后的代码如下：

1. Sub ado方法()
   
2.     Set conn = CreateObject("ADODB.Connection")   '
   
3.     Set rst = CreateObject("ADODB.Recordset")  '
   
4.     conn.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
   
5.     bm1 = "select 资产编码 from [期初清单$] union all select 资产编码 from [期末清单$] union all select 资产编码 from [明细账$]"
   
6.     bm = "select distinct 资产编码 from (" & bm1 & ")"
   
7.     s1 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期初清单$]"
   
8.     s2 = "select 资产编码,原值本币 as bg原值,累计折旧 As bg折旧 from [期末清单$]"
   
9.     s3 = "select 资产编码,原值(综合本位币):借方金额 as dr原值,'原值(综合本位币):贷方金额' As cr折旧,'累计折旧(综合本位币):借方金额' as dr折旧 from [明细账$]"
   
10.     s4 = "select t1.资产编码 as 资产编码,* from (" & bm & ") t1 left join (" & s1 & ") t2 on t1.资产编码=t2.资产编码"
    
11.     s5 = "select * from (" & s4 & ") t3 left join (" & s2 & ") t4 on t3.资产编码=t4.资产编码"
    
12.     s = "select * from (select t1.资产编码 from (" & bm & ") t1 left join (" & s1 & ") t2 on t1.资产编码=t2.资产编码) t1 left join (" & s1 & ") t2 on t1.资产编码=t2.资产编码"
    
13.     s = "select * from (" & bm & ") t1,(" & s1 & ") t2,(" & s2 & ") t3 where t1.资产编码=t2.资产编码 and t1.资产编码=t3.资产编码"
    
14.     Set rst = conn.Execute(s5)
    
15.     Sheets("表").[a2].CopyFromRecordset rst
    
16. end sub

复制代码

amazeyeli

05050818 发表于 2019-1-23 17:00
真心没看懂，能解释一下吗

全连接=左连接+右连接
试一下就知道了

amazeyeli

hyz00001 发表于 2019-1-23 17:02
感谢各位，刚才那个问题我自己想明白了。可以通过多重连接来实现三张表left join查询。

但另外两个问 ...

非法字段名用[]括起来
Sub test()

    Dim cnn As Object, rst As Object

    Dim Sql As String, i As Long

    Set cnn = CreateObject("adodb.connection")

        cnn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Extended Properties = Excel 12.0;Data Source = " & ThisWorkbook.FullName

        Sql = "SELECT * FROM [Sheet1$]"
   
    Set rst = cnn.Execute(Sql)

    [a:i].ClearContents
   
    For i = 0 To rst.Fields.Count - 1

        Cells(1, i + 1) = rst.Fields(i).Name

    Next

    Range("a2").CopyFromRecordset rst
   
    cnn.Close

    Set cnn = Nothing

End Sub



[a:i].ClearContents
   
    For i = 0 To rst.Fields.Count - 1

        Cells(1, i + 1) = rst.Fields(i).Name