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

zhaogang1960

opiona 发表于 2011-11-16 19:21
Range("a2").CopyFromRecordset y

SQL查询只有一个字段，就是一列

请参阅Scarlett_88版主的
TRANSFORM 语句的详解
http://club.excelhome.net/thread-462590-1-1.html

opiona

先感谢，在学习！

不是很明白，但是成功了！
Strsql = "Select [压实度（%）] From [压实度$a18:j29]"   
这是原来的SQL语句
Strsql = "TRANSFORM sum([压实度（%）])  SELECT  编号 FROM [压实度$a18:j29] GROUP BY 0 PIVOT 编号"
这是转置的SQL
要求字段：编号 是不重复的。
关键是这里的0：GROUP BY 0

opiona

2011-压实度-000004.rar (15.11 KB, 下载次数: 126)

2011-11-16 22:38 上传

点击文件名下载附件

总体是成功的，但是不知道为什么前面多出来2个空值
代码在附件内

zhaogang1960

对TRANSFORM语句没有研究，加个条件where 编号 is not null：

1. Sub OPIONA()
   
2. 
   
3. Application.ScreenUpdating = False '关闭屏幕刷新
   
4. Application.DisplayAlerts = False '关闭提示
   
5. Dim Strsql$, Cn As Object
   
6. Set y = CreateObject("adodb.recordset")
   
7. Set Cn = CreateObject("Adodb.Connection")
   
8. Set sh1 = ThisWorkbook.Sheets("汇总表")
   
9. 
   
10. Cn.Open "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;HDR=yes;imex=2';data source=" & ThisWorkbook.FullName
    
11. 'Strsql = "Select [压实度（%）] From [压实度$a18:j29]"    '获取
    
12. Strsql = "TRANSFORM sum([压实度（%）]) as YY  SELECT  编号 FROM [压实度$a3:j14] where 编号 is not null GROUP BY 0 PIVOT 编号"
    
13. 
    
14. Set y = Cn.Execute(Strsql)
    
15. sh1.Range("b5:L5").ClearContents
    
16. 
    
17. For i = 0 To y.Fields.Count - 1
    
18. Cells(4, i + 2) = y.Fields(i).Name
    
19. Next i
    
20. 
    
21. sh1.Range("b5").CopyFromRecordset y
    
22. 
    
23. Cn.Close
    
24. 
    
25. Application.ScreenUpdating = True
    
26. Application.DisplayAlerts = True
    
27. End Sub
    

复制代码

opiona

又来SQL的问题了：
我想在SQL的查询结果中继续查询，试图将附件中的代码写成一句SQL：

1. SQL = " SELECT 日期,sum(数值合计),sum(数值个数),sum(合计),sum(个数) from  "
   
2. SQL = SQL & " (SELECT 日期,sum(数值) as 数值合计,COUNT(数值) as 数值个数,0,0 from [Sheet1$a1:b18] where 数值>0 GROUP BY 日期 "
   
3. SQL = SQL & " Union ALL"
   
4. SQL = SQL & " select 日期,0,0,sum(数值) as 合计,COUNT(数值) as 个数 from [Sheet1$a1:b18] where 数值<0 GROUP BY 日期)"
   
5. SQL = SQL & " GROUP BY 日期"

复制代码

我的本意是：将两个分类汇总（一个是大于0的合计和个数，另一个是小于0的合计和个数）
连接成一个5个字段的数据表：日期、数值合计、数值个数、合计、个数
然后在用SQL在这个虚拟（不可见）的数据表中查询
但是总是提示错误，大侠帮忙看看！
用SQL在SQL查询结果中查询.rar (13.86 KB, 下载次数: 91)

2011-11-18 22:27 上传

点击文件名下载附件

写成下面的样子也不行，结果全是大于0的，：

1. SQL = " SELECT 日期,sum(数值合计),sum(数值个数),sum(合计),sum(个数) from  "
   
2. SQL = SQL & " (SELECT 日期,sum(数值) as 数值合计,COUNT(数值) as 数值个数,sum(数值) as 合计,COUNT(数值) as 个数 from [Sheet1$a1:b18] where 数值>0 GROUP BY 日期 "
   
3. SQL = SQL & " Union ALL"
   
4. SQL = SQL & " select 日期,sum(数值) as 数值合计,COUNT(数值) as 数值个数,sum(数值) as 合计,COUNT(数值) as 个数 from [Sheet1$a1:b18] where 数值<0 GROUP BY 日期)"
   
5. SQL = SQL & " GROUP BY 日期"

复制代码

大侠给了另一种写法：

1. SQL = "select 日期,sum(iif(数值>0,数值,0)),sum(iif(数值>0,1,0)),sum(iif(数值<0,数值,0)),sum(iif(数值<0,1,0)) from [sheet1$a1:b18] group by 日期"
   

复制代码

还是想知道：
用SQL的查询结果临时建立一个表，用SQL在这个虚拟（不可见）的数据表中继续另外一个查询
一般SQL怎么写呢？  能否发几个经典例句。

zhaogang1960

opiona 发表于 2011-11-18 22:30
又来SQL的问题了：
我想在SQL的查询结果中继续查询，试图将附件中的代码写成一句SQL：
我的本意是：将两个 ...

对SQL研究不够，可以在论坛上搜一下：
SQL = "SELECT 日期,sum(iif(数值>0,数值,0)) as 数值合计,sum(iif(数值>0,1,0)) as 数值个数,sum(iif(数值<0,数值,0)) as 合计,sum(iif(数值<0,1,0)) as 个数 from [Sheet1$a1:b18] GROUP BY 日期"

opiona

这句我看明白了，也测试成功，没有问题，十分简练的SQL语句
我想知道的是：
如何 连接几个SQL查询结果组成一个数据表，
第一个查询结果的字段： 日期 大于0数值的和 大于0数值的个数
第二个查询结果的字段： 日期 小于0数值的和 小于0数值的个数
想用他们建立一个新表，字段为：日期 大于0数值的和 大于0数值的个数 小于0数值的和 小于0数值的个数

如何使两个查询结果都变成含5个字段的样子
见过使用0 或者null占位的用法，具体如何使用呢？
SUM（数值）等函数组成的字段，用什么占位呢？

zhaogang1960

opiona 发表于 2011-11-19 11:27
这句我看明白了，也测试成功，没有问题，十分简练的SQL语句
我想知道的是：
如何 连接几个SQL查询结果组成 ...

SQL = " SELECT 日期,sum(数值合计),sum(数值个数),sum(合计),sum(个数) from  "
SQL = SQL & " (SELECT 日期,sum(数值) as 数值合计,COUNT(数值) as 数值个数,0 as 合计,0 as 个数 from [Sheet1$a1:b18] where 数值>0 GROUP BY 日期 "
SQL = SQL & " Union ALL"
SQL = SQL & " select 日期,0 as 数值合计,0 as 数值个数,sum(数值) as 合计,COUNT(数值)as 个数 from [Sheet1$a1:b18] where 数值<0 GROUP BY 日期) group by 日期"

opiona

高手就是高手，一出手就知有没有！

1. 在SQL语句中可以使用 0 或者 null 占据字段位置，
   
2. 0 as 字段名
   
3. null as 字段名
   
4. 
   
5. SQL = " SELECT 日期,null,sum(数值个数),0 as 合计,sum(个数) from  [Sheet1$]"
   
6. 在查询结果中相应字段位置就会全部是0 或者 null
   
7. 
   
8. SQL = " SELECT 日期,sum(数值合计),sum(数值个数),sum(合计),sum(个数) from  "
   
9. SQL = SQL & " (SELECT 日期,sum(数值) as 数值合计,COUNT(数值) as 数值个数,0 as 合计,0 as 个数 from [Sheet1$a1:b18] where 数值>0 GROUP BY 日期 "
   
10. SQL = SQL & " Union ALL"
    
11. SQ

复制代码

opiona

Book1.rar (552.56 KB, 下载次数: 77)

2011-11-27 22:25 上传

点击文件名下载附件

这里有两个表，字段为：英语，外语
目的是：以英语为联系纽带，获得其他两种语言的对应关系
在Sheet2查找Sheet1中的英语，将查到的结果（俄语）按照对应英语在Sheet1的顺序，粘贴到C列
这样ABC三列就是英语，汉语，俄语对应关系了。
（SQL写的查询结果是Sheet2的英语，是因为俄语难以校对结果是否正确）

SQL = "select b.工资 from [Sheet1$] a left join [Sheet2$] b on a.姓名=b.姓名"
斑竹给的这句我一直在用，效果很好，
但是在这个表内却不好用，查询到的结果和原来的Sheet1的英语顺序对不上。

使用公式：=VLOOKUP(A:A,Sheet2!A:B,2,1)  结果就没有问题

1. 原来原因是英语中有重复数据啊！
   
2. 现在先提取不重复数据，组建两个新表，然后再执行版主的SQL，好用！ 哈哈！
   
3.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no';Data Source=" & ThisWorkbook.FullName
   
4.     SQL = ""
   
5.     SQL = SQL & "select b.英语,a.外语,b.外语 from ("
   
6.     SQL = SQL & "select DISTINCT f1 as 英语,f2 as 外语 from [Sheet1$]"
   
7.     SQL = SQL & ") a left join ("
   
8.     SQL = SQL & "select DISTINCT f1 as 英语,f2 as 外语  from [Sheet2$]"
   
9.     SQL = SQL & ") b on a.英语=b.英语"

复制代码