ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 11975|回复: 37

[分享] 我的SQL学习贴

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2016-2-17 11:25 | 显示全部楼层 |阅读模式
本帖最后由 autumnalRain 于 2016-3-25 08:52 编辑

对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



















评分

参与人数 1鲜花 +2 收起 理由
wmqz.130 + 2 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 11:29 | 显示全部楼层
赵老师是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
复制代码

统计分数大于90人数.zip

18.04 KB, 下载次数: 123

TA的精华主题

TA的得分主题

发表于 2016-2-17 11:38 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 12:06 | 显示全部楼层
如何获取查询结果中的指定行的记录
代码同样来自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
复制代码

获取查询结果中的指定行记录.zip

18.22 KB, 下载次数: 97

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 12:18 | 显示全部楼层
本帖最后由 autumnalRain 于 2016-2-17 14:50 编辑

求助贴:后进先出法下结转成本,如何用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


后进先出法如何用SQL语句获取单价.zip

8.54 KB, 下载次数: 63

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 13:12 | 显示全部楼层
本帖最后由 autumnalRain 于 2016-2-17 13:17 编辑

按照每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
复制代码






把查询结果每35行分一次列.rar

76.83 KB, 下载次数: 73

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 13:23 | 显示全部楼层
本帖最后由 autumnalRain 于 2016-2-17 13:32 编辑

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 & "# "
-------------------------------------------------------------------------------------------------------------------------------------------
第二句中""相当于一个单引号作用

[size=17.1428565979004px]

TA的精华主题

TA的得分主题

发表于 2016-2-17 14:23 | 显示全部楼层
autumnalRain 发表于 2016-2-17 12:18
求助贴:后进先出法下结转成本,如何用SQL语句得出下面的单价?请大师指导!

没看懂,a 10 为什么是2016-1-7?

TA的精华主题

TA的得分主题

 楼主| 发表于 2016-2-17 14:30 | 显示全部楼层
本帖最后由 autumnalRain 于 2016-2-17 14:51 编辑
魂断蓝桥 发表于 2016-2-17 14:23
没看懂,a 10 为什么是2016-1-7?

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

TA的精华主题

TA的得分主题

发表于 2016-2-17 15:06 | 显示全部楼层
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.名称

评分

参与人数 1鲜花 +2 收起 理由
autumnalRain + 2 感谢帮助

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-22 00:34 , Processed in 0.709354 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表