我的SQL学习贴

autumnalRain

对SQL接触了一段时间，参考书是《EXCEL 2010 SQL完全应用》，但是仍然发现很多用法书上没有，在此将论坛搜索到的例子贴出来，作为本人的学习贴。菜鸟可以参考，高手飘过。谢谢……

整理的学习贴：
1、从零开始学习SQL(图文教程) http://club.excelhome.net/thread-1061487-1-1.html
2、分享学习sql语句 http://club.excelhome.net/thread-1193588-1-1.html
3、杂谈 ADO http://club.excelhome.net/thread-1233692-1-1.html
4、在EXCEL中使用SQL语句查询集锦http://club.excelhome.net/thread-859194-1-1.html
5、CopyFromRecordset 资料整理 http://club.excelhome.net/thread-275559-1-1.html
6、关于Recordset的GetRows的用法问题 http://club.excelhome.net/thread-729918-1-1.html
7、SQL语句：如何从EXCEL工作表中实现查询 http://club.excelhome.net/thread-984392-1-1.html
8、根据excel表数据更新有重复记录的数据库并插入数据库中不存在的记录 http://club.excelhome.net/thread-1023636-1-1.html
9、来来来，我们来学SQLhttp://club.excelhome.net/thread-461997-1-1.html
10、SQL循序渐进---基础好书 http://club.excelhome.net/thread-984392-1-1.html
11、ADO学习整理http://club.excelhome.net/thread-1005768-1-1.html
12、使用 ADO-SQL 处理 EXCEL 文件的程序架构（实例注释）http://club.excelhome.net/thread-160847-1-1.html
13、Excel+ADO(DAO)+SQL+Access专辑（编辑中...）http://club.excelhome.net/thread-70082-1-1.html
14、 SQL 中SUM(1)与COUNT(1)区别http://club.excelhome.net/thread-1069908-1-1.html
15、
16、不用VBA，不用SQL语句，且看 POWER QUERY 快速合并多个Excel工作簿http://club.excelhome.net/thread-1190917-1-1.html
17、进一步综合介绍 Power Query 使用方法 http://club.excelhome.net/thread-1070381-1-1.html
18、用SQL汇总或合并工作表、工作簿和跨文件夹和工作表汇总http://club.excelhome.net/thread-1170256-1-1.html



。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
MS Query汇总多个工作簿数据(基础操作法)http://club.excelhome.net/thread-297474-1-1.html








========================================================================================================

我的提问贴：
1、GETROWS方法得到的数组为什么不可以直接转置写入到单元格中http://club.excelhome.net/thread-1240753-1-1.html
2、相同的SQL语句| 有时可以运行|有时报错|VBA或使用“现有连接”方法都出现过http://club.excelhome.net/thread-1238051-1-1.html
3、探讨如何获得RECORDSET正确记录数http://club.excelhome.net/thread-1223155-1-1.html
4、请高手指点VBA+SQL中where子句使用两个条件变量如何书写http://club.excelhome.net/thread-1219094-1-1.html
5、帮助看下VBA+SQL中为什么SQL语句中的*不可以换成具体字段名称呢?http://club.excelhome.net/thread-1216710-1-1.html
6、VBA+SQL批量打印直拨单程序如何控制分页？ http://club.excelhome.net/thread-1219272-1-1.html

autumnalRain

赵老师是ADO专家，学到了很多知识，下面这个例子简单，但是对我来说是个全新的知识点儿，SQL还可以这样用！

1. Sub ADO法()
   
2.     With CreateObject("ADODB.Connection")
   
3.         .Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
4.         MsgBox "语文>=90分的人数：" & .Execute("SELECT count(语文) FROM [Sheet1$] WHERE 语文>=90").Fields(0)
   
5.         MsgBox "数学>=90分的人数：" & .Execute("SELECT count(数学) FROM [Sheet1$] WHERE 数学>=90").Fields(0)
   
6.     End With
   
7. End Sub

复制代码

tanchuncheng

谢谢，学习了

autumnalRain

如何获取查询结果中的指定行的记录
代码同样来自zhaogang1960,也是第一次知道SQL可以这样获取指定的记录

1. Sub 如何使用VBA获取某几行记录()
   
2.     Dim AdoConn As New ADODB.Connection
   
3.     Dim AdoRst As ADODB.Recordset
   
4.     Dim strConn As String
   
5.     Dim strSQL As String
   
6.     Dim i As Integer
   
7.     '清空表格
   
8.     Sheets("查询结果").Cells.Clear
   
9.     '设置连接字符串
   
10.     strConn = " Provider=Microsoft.ACE.OLEDB.12.0;" & _
    
11.           "Data Source=" & ThisWorkbook.FullName & _
    
12.           ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
13.     '设置SQL查询语句
    
14.     strSQL = "Select 产销国家或地区,地区, 出口金额 FROM [出口数据$]  Order By 出口金额 DESC"
    
15.     '打开数据库链接
    
16.     AdoConn.Open strConn
    
17.     '执行查询，并将结果输出到记录集对象
    
18.     Set AdoRst = AdoConn.Execute(strSQL)
    
19.     '将当前指针下移10条
    
20.     AdoRst.Move 10
    
21.     '用GetRows从当前记录开始选取5条记录作为二维数组输出到工作表A2:C6中
    
22.     Sheets("查询结果").Range("A2:C9") = WorksheetFunction.Transpose(AdoRst.GetRows(8, 0))
    
23.     '填写标题
    
24.     For i = 0 To AdoRst.Fields.Count - 1
    
25.         Sheets("查询结果").Cells(1, i + 1) = AdoRst.Fields(i).Name
    
26.     Next i
    
27.     '关闭数据库连接
    
28.     AdoRst.Close
    
29.     AdoConn.Close
    
30. End Sub

复制代码

autumnalRain

求助贴：后进先出法下结转成本，如何用SQL语句得出下面的单价？请大师指导！


采购价》》

采购日期	名称	采购单价
2016/1/1	A	11
2016/1/2	C	12
2016/1/3	B	47
2016/1/4	A	10
2016/1/5	D	43
2016/1/6	C	20
2016/1/7	D	50
2016/1/8	E	18
2016/1/9	E	20

出库表结转成本》》本表中最近采购单价是模拟的，SQL语句要查询的就是此列的单价信息。条件是采购日期<=出库日期，但是满足条件的不止一条记录，需要得出最近的一条价格信息

出库日期	名称	最近采购单价
2016/1/7	A	10
2016/1/9	C	20
2016/1/10	D	50
2016/1/12	E	20
2016/1/13	B	47

autumnalRain

按照每35行分一次列，写到[个人捐款汇总$]中，查询条件为活动工作表的科目[F1]日期介于[B2]和[D2]间。
本例可以应用到批量打印中，如固定格式的单据中最多一次打印多少行
应该重点学习的内容是依次取得第一个35条记录，第二个35条记录……第N个35条记录，如何写代码见代码第8-11行。特别需要强调的一句话：
使用CopyFromRecordset方法时，复制从 Recordset 对象的当前行开始的内容。复制完成之后，Recordset 对象的 EOF 属性值为 True。
赵老师的代码解决了一直以来的疑惑，在此转到本贴，当做学习笔记！

1. Sub ADO法()
   
2.     Dim cnn As Object, rs As Object, SQL$, i&
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     cnn.Open "Provider = Microsoft.Jet.Oledb.4.0;Extended Properties ='Excel 8.0;hdr=no';Data Source =" & ThisWorkbook.FullName
   
5.     SQL = "Select f1,f3,f6,f7 from [收支记录$a4:g]  where f2='" & [f1] & "' and f1 between #" & [b2] & "# and #" & [d2] & "#"
   
6.     Set rs = cnn.Execute(SQL)
   
7.     [a1].CurrentRegion.Offset(3).ClearContents
   
8.     While Not rs.EOF
   
9.          Cells(4, i + 1).CopyFromRecordset rs, 35
   
10.          i = i + 4
    
11.     Wend
    
12.     rs.Close
    
13.     cnn.Close
    
14.     Set rs = Nothing
    
15.     Set cnn = Nothing
    
16. End Sub

复制代码

autumnalRain

SQL查询中的特殊字符处理
******************************************************************************************************************************************************************
1、SQL查询过程中，单引号“'”是特殊字符。所以在查询的时候要转换成双单引号“''”。在sql中'是条件符，如果条件中还用到'就需要用两个了。一个作为条件符，一个作为字符本身。记住是两个'不是一个"。如select * from a where type="j's dog”报错，处理方法为：select * From a where  A_ID='j''s dog'
2、但这只是特殊字符的一个，在实际项目中，发现对于like操作还有以下特殊字符：下划线“_”，百分号“%”，方括号“[]”以及尖号“^”。
其用途如下：  
下划线：用于代替一个任意字符（相当于正则表达式中的 ? ）
百分号：用于代替任意数目的任意字符（相当于正则表达式中的 * ）
方括号：用于转义（事实上只有左方括号用于转义，右方括号使用最近优先原则匹配最近的左方括号）
尖号：用于排除一些字符进行匹配（这个与正则表达式中的一样）

下表显示了括在方括号内的通配符的用法。

符号                         含义
LIKE '5[%]'               5%
LIKE '5%'                  5 后跟 0 个或更多字符的字符串
LIKE '[_]n'                 _n
LIKE '_n'                   an, in, on (and so on)
LIKE '[a-cdf]'             a, b, c, d, or f
LIKE '[-acdf]'             -, a, c, d, or f
LIKE '[ [ ]'                 [


在SQL中方括号作为转义符使用，比如如果列字段中存在这样的字段时  速度米/秒，写语句时应规范为 [速度米/秒] 或者`速度米/秒`   表示这里的/是作为字符使用，而不是特殊字符
使用用列别名时，如果别名中包含空格或其它特殊字符，则列别名必须放在单引号，双引号或方括号中

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL列字段命名的限制：
当使用SQL语句的时候，必须避免列字段中使用下面的特殊字符：
空格，双引号（"）、撇号（'）、重音符（`），数字符号（#），百分号（%）、大于号（>），小于号（<），叹号（!），句号（.），方括号（[or]），号号（*），美元符（$），分号（;），脱字符（^），圆括号（(or)），加号（+），反斜杠（\or/）等
如果在源数据表的列字段使用了这些特殊符号，那么在使用SQL语句列出列字段的数据时，就会发生错误。

***********************************************************************************************************************
SQL语句中注意：文本变量（和文本常量）要用单引号，数字类型的不能加单引号，日期类型的需要两边加#：
***********************************************************************************************************************

SQL = "select 凭证号,名称,规格,单位,数量,单价,金额,项目名称 from [项目名称$] where [凭证号]='" & SVoucher & "' And [项目名称]= '" & SProject & "'"
Sql = "select 凭证号,名称,规格,单位,数量,单价,金额,项目名称 from [项目名称$] where [凭证号]=""" & SVoucher & """ And [项目名称]= """ & SProject & """"
sql = "Select * from  table1  Where  日期 between #" & ld_1 & "# and #" & ld _2 & "# "
-------------------------------------------------------------------------------------------------------------------------------------------
第二句中""相当于一个单引号作用

魂断蓝桥

autumnalRain 发表于 2016-2-17 12:18
求助贴：后进先出法下结转成本，如何用SQL语句得出下面的单价？请大师指导！

没看懂，a 10 为什么是2016-1-7？

autumnalRain

魂断蓝桥 发表于 2016-2-17 14:23
没看懂，a 10 为什么是2016-1-7？

不好意思，老师，我没有解释清楚，作出下说明：
第一个表信息是采购价信息表，日期是采购日；第二表中，出库日期是指的实际销售日期，这个日期与采购日期可能不一样。采购日期<=出库日期。要获取的价格是在销售日期前也可以是同一天的最近的一个采购价。比如
2016/1/7 A产品出库，要获得与2016/1/7日最近的一次采购价，即2016/1/4日的价格10，虽然还有一个价格2016/1/1，11，但它不是与销售日期最近的价格。存货计价方法里的后进先出法。也就是说，出库表中要获取的价格条件是采购日期<=出库日期

魂断蓝桥

autumnalRain 发表于 2016-2-17 14:30
不好意思，老师，我没有解释清楚，作出下说明：
第一个表信息是采购价信息表，日期是采购日；第二表中， ...

这样试一下

采购表 定义名称ttt
出库表 定义名称aaa

select a.t1 from (select 名称,last(单价) as t1 from ttt group by 名称) a,aaa b where a.名称=b.名称