关于SQL在工作簿-工作表之间的查询、排序、转置（盖楼中....）

opiona

'查找查询表给出字段内容（sheet1的A列）在数据表中查找对应的其他字段内容（Sheet2的B列：工资）
Strsql = "SELECT a.姓名, b.工资 from [Sheet2$] as b, [Sheet1$] as a where a.姓名=b.姓名 order by a.姓名"
'工作簿内的查询我已经实现，工作表之间的不想使用循环
'1：想知道: 如果sheet1 和 Sheet2 分别在2个工作簿，SQL应该怎么写？
'这里的SQL的：[数据表.Sheet1$], [查询表.Sheet1$]  不会写


'工作簿内的查询我已经实现，但是查询结果的顺序是Sheet3的顺序
'2：想知道：有没有办法SQL直接给出 按照sheet1中A列顺序 排序的结果

代码弄了一半，剩下的大家帮忙
工作簿之间查询SQL.rar (20.22 KB, 下载次数: 498)

2011-11-14 19:29 上传

点击文件名下载附件

.



该贴已经同步到 opiona的微博

opiona

第一个答案找到了：

1. Sub 工作簿之间汇总() '
   
2. Application.ScreenUpdating = False '关闭屏幕刷新
   
3. Application.DisplayAlerts = False '关闭提示
   
4. Dim Strsql$, Cn As Object
   
5. Set y = CreateObject("adodb.recordset")
   
6. Set Cn = CreateObject("Adodb.Connection")
   
7. t = Timer
   
8. Sheet3.Cells.Clear
   
9. Cn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
   
10. Dataname = ThisWorkbook.Path & "\数据表.xls"
    
11. 'Strsql = "SELECT a.姓名, b.工资 from [Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b]  as b, [Sheet1$a:b] as a where a.姓名=b.姓名 order by a.姓名"
    
12. Strsql = "SELECT  姓名, 工资 from [Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b] where  姓名 in (select 姓名 from [Sheet1$a:b] )"
    
13. '答案找到了：[Excel 8.0;Database=" & Dataname & "].[Sheet1$a:b]
    
14. '甚至都不用打开工作簿
    
15. '两种SQL写法都达到查询目的
    
16. 
    
17. Set y = Cn.Execute(Strsql)
    
18. Windows(ThisWorkbook.Name).Activate '回到操作主表界面
    
19. Sheet3.Range("a2").CopyFromRecordset y
    
20. Cn.Close
    
21. 
    
22. Sheet3.Select
    
23. Application.ScreenUpdating = True
    
24. Application.DisplayAlerts = True
    
25. MsgBox "一共用时：" & Format(Timer - t, "#0.00") & "秒"
    
26. 
    
27. End Sub

复制代码

等待大侠对第二问题的解答，或者是没有办法如此排序？？

zhaogang1960

外连接：
Sub Macro1()
    Dim cnn, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\数据表.xls"
    SQL = "select b.工资 from [Excel 8.0;Database=" & ThisWorkbook.FullName & "].[Sheet1$] a left join [Sheet1$] b on a.姓名=b.姓名"
    Range("b2").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub

opiona

感谢版主！
原来SQL果真如此神奇！

1. '连接数据库语句在EXCEL 2007和2003的区别：
   
2. '    Microsoft.ACE.OLEDB.12.0      Microsoft.jet.OLEDB.4.0
   
3. '    Excel 12.0                    Excel 8.0
   
4. '    外部链接：[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[Sheet1$]     Excel 8.0;Database=" & ThisWorkbook.FullName & "].[Sheet1$]
   
5. '2007可以查询2003，但是2003不能查询2007
   
6. Sub Macro2007()
   
7.     Dim cnn, SQL$
   
8.     Set cnn = CreateObject("adodb.connection")
   
9.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\数据表.xlsx"
   
10.     SQL = "select b.工资, b.补贴 from [Excel 12.0;Database=" & ThisWorkbook.FullName & "].[Sheet1$] a left join [Sheet1$] b on a.姓名=b.姓名"
    
11. '   ThisWorkbook.Path & "\数据表.xls--------b表是数据表，
    
12. '   ThisWorkbook.FullName--------a表是条件表，
    
13. '   按照a表顺序，在b表查找，结果按照a表顺序粘贴（原顺序不表，结果对应粘贴）
    
14.     Range("b2").CopyFromRecordset cnn.Execute(SQL)
    
15.     cnn.Close
    
16.     Set cnn = Nothing
    
17. End Sub

复制代码

opiona

按照时间段SQL求和.rar (3.97 KB, 下载次数: 164)

2011-11-15 17:37 上传

点击文件名下载附件

表中一个字段为：日期+时间
日期格式为：2011-10-10  18:16:38
求：时间在9：00--12：00之间的数据SQL求和，不考虑日期

日期的用：字段 between 日期1 and 日期2
时间的不会写了，
请帮忙！！！！

zhaogang1960

opiona 发表于 2011-11-15 17:38
表中一个字段为：日期+时间
日期格式为：2011-10-10  18:16:38
求：时间在9：00--12：00之间的数据SQL求 ...

Sub Macro1()
    Dim cnn, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
    SQL = "select sum(消费金额) from [Sheet1$] where timevalue(right(消费时间,8)) between #9:00# and #12:00#"
    MsgBox cnn.Execute(SQL)(0)
    cnn.Close
    Set cnn = Nothing
End Sub

zhaogang1960

请看附件


按照时间段SQL求和.rar (10.23 KB, 下载次数: 395)

2011-11-15 19:32 上传

点击文件名下载附件

opiona

{:soso_e157:}斑竹：
我对你的敬仰，如滔滔江水........................

南海

记号学习学习

opiona

Range("a2").CopyFromRecordset y

SQL查询只有一个字段，就是一列
如果查询结果的个数不确定
结果粘贴到表中，就是很多行的那种

怎么做才能：
结果粘贴到表中变成一行，和工作表行列转置相似
好像见过，找不到了。

终于找到了那个行列转置的代码：

1. Strsql = "SELECT  编号, sum([压实度（%）])  FROM [压实度$a3:j14]  where 编号 is not null  GROUP BY 编号"
   
2. Set y = Cn.Execute(Strsql)
   
3. '--------查询结果行列转置------------------------
   
4. Arr = y.Getrows '把cnn记录赋值为二维数组
   
5. 'MsgBox "OK " & UBound(Arr) & "-" & UBound(Arr, 2)
   
6. sh1.Cells(5, 2).Resize(UBound(Arr) + 1, UBound(Arr, 2) + 1) = Arr
   
7. '关键语句：按照Arr大小，调整指定区域的大小

复制代码