EXCELOLEDB查询下的SQL函数整理whsfhwm.docx
《EXCELOLEDB查询下的SQL函数整理whsfhwm.docx》由会员分享，可在线阅读，更多相关《EXCELOLEDB查询下的SQL函数整理whsfhwm.docx（21页珍藏版）》请在冰豆网上搜索。
EXCELOLEDB查询下的SQL函数整理whsfhwm
目录
一、前言3
1.背景简介3
2.适用范围：
4
3.技巧介绍4
4.SQL函数分类5
二、聚合函数6
1.Sum函数6
2.Count函数8
3.Avg函数9
4.Min和Max函数10
5.First和Last函数11
6.StDev和StDevP函数12
7.Var和VarP函数13
三、字符串相关函数13
（一）字符串查找函数13
1.Instr函数13
2.InStrB函数15
（二）字符串转换函数15
1.CBool（expression）15
2.CByte（expression）15
3.CCur（expression）15
4.CDate（expression）15
5.CDbl（expression）15
6.CDec（expression）15
7.CInt（expression）15
8.CLng（expression）15
9.CSng（expression）15
10.CStr（expression）15
11.CVar（expression）15
12.Val（string）16
13.Str（number）16
（三）字符串处理函数17
1.Trim（string）17
2.Ltrim（string）17
3.Rtrim（string）17
4.Len（string）17
5.Left（string,x）17
6.Right（string,x）17
7.Mid（string,start,x）17
8.Ucase（string）18
9.Lcase（string）18
10.Space（x）18
11.Asc（string）18
12.AscB（string）18
13.AscW（string）18
14.Chr（charcode）18
15.ChrW（charcodeaslong）18
16.String（number,string）18
17.LeftB（String,LengthAsLong）18
18.RightB（String,LengthAsLong）19
19.MidB（String,StartAsLong,[Length]）19
20.LenB（Expression）19
21.Format（expression[,format[,firstdayofweek[,firstweekofyear]]]）19
22.StrComp（string1,string2[,compare]）20
23.StrConv（string,conversion,LCID）21
四、数学函数22
1.Sin（X）、Cos（X）、Tan（X）、Atan（x）22
2.Log（x）22
3.Exp（x）22
4.Abs（x）返回绝对值。
22
5.Int（number）、Fix（number）22
6.Sgn（number）22
7.Sqr（number）22
8.VarType（varname）22
9.Rnd（x）22
10.ROUND（c,decimals）22
五、测试函数23
1.IsNumeric（x）23
2.IsDate（x）23
3.IsEmpty（x）23
4.IsError（expression）23
5.IsNull（expression）23
6.IsObject（identifier）23
六、日期时间函数23
1.日期加减：
DateAdd函数23
2.计算两个日期的差DateDiff函数：
24
3.提取日期中的部分内容DatePart函数：
24
4.用独立的部件生成日期DateSerial函数：
26
5.用字符串表达式生成日期：
DateValue（stringexpression）26
6.Now（）26
7.Date（）26
8.Time（）26
9.Timer（）26
10.TimeSerial（hour,minute,second）26
11.DateDiff（interval,date1,date2[,firstdayofweek[,firstweekofyear]]）27
12.Second（time）27
13.Minute（time）27
14.Hour（time）27
15.Day（date）27
16.Month（date）27
17.Year（date）27
18.Weekday（date,[firstdayofweek]）27
七、流程控制函数27
1.Iif（）和Switch（）27
八、结束语28
一、前言
1.背景简介
一直想对EXCEL2003中SQL语句里的可用函数进行专门的归纳整理，今天终于与大家见面了。
虽然目前EXCEL2007/2010使用者越来越多，但无疑EXCEL2003依然有着大量的使用人群，所以我才觉得很有必要对此进行整理，方便SQL初学者，希望他们能够少走一些弯路，节约一点时间来学习更多的精彩内容。
相信对于使用高版本的朋友来说，本文也是有一定的借鉴作用的。
构成本文的内容，有相当部分是来自于EH培训中心SQL初级培训班的培训过程，有引自EH技术论坛的内容，也有来自微软提供的帮助文档，当然还有本人自己钻研的一点点心得体会，所以这里主要是一种归纳整理。
由于本人水平所限，学习SQL时间也不长，所以有许多地方理解还比较肤浅，也难免有所疏漏或谬误，欢迎大家批评指正，或给与补充。
本文中，如果没有特别说明，则所提到的SQL函数都是已经经过本人在EXCEL2003中OLEDB环境下测试并通过的；文中提到的示例语句一般使用蓝色字体，由于示例太多的话，将会让EXCEL文件过于庞大，故只在EXCEL文件“EXCEL-OLEDB之SQL函数示例（whsfhwm整理）.xls”里提供了一部分实例，方便大家进行测试，文中出现的示例，而上述EXCEL文件中又没有的，完全可以自己做测试。
另外，由于SQL语句的语法对于大小写不敏感，所以本文中并没有对SQL语句的书写进行严格规范，这一点请大家多多包涵。
2.适用环境：
本文介绍的内容适用的环境为：
EXCEL2003OLE-DB环境下的SQL查询。
本文内容重点在于介绍SQL函数，而非SQL语句。
3.技巧介绍
介绍SQL函数之前，先介绍一下本人在测试SQL语句中的几个小技巧：
●编辑技巧：
（虽然不是本人首创，但接触SQL多了，自然而然就需要这样做了）使用记事本或其他类似小巧的编辑工具来编辑SQL语句。
通常，你会感觉到编辑查询对话框中进行编辑SQL语句实在是不方便，因此推荐在记事本中进行编辑，好处有两点：
一是可以方便的将数据源的标题行复制过来，然后用替换的方法将字段名称之间转化为“,”，这个是SQL语句语法的要求，这样复制过来的字段名称还可以避免自己录入出错的可能性；二是可以防止在所书写SQL语句有错误的情况下丢失刚才的语句，起到临时书写备份的作用，便于自己对错误语句的检查。
●首次创建查询表技巧：
第一次创建导入外部数据的查询表时，不得不通过菜单-导入外部数据-导入数据-选择数据源文件-出现“编辑OLEDB查询”对话框时，此时一般就不要匆忙将自己书写的SQL查询语句录入到命令文本中，而是采用默认的方式生成查询表，这样做的好处就是避免自己所写的SQL语句有误，不产生任何结果，然后下一次操作又要重复上面的步骤，效率显然较低；所以建议采用默认方式，然后再通过右键快捷菜单打开编辑查询对话框进行修改测试。
●复制SQL查询表技巧：
针对同一数据源创建多个SQL查询表时，第二个及以后的查询表可以不用通过菜单操作，而直接把第一个SQL查询表的全部区域进行复制（显然比通过菜单操作要快得多），然后修改SQL语句即可。
注意：
当你的被复制的SQL查询表只返回一个值时，也就是只有一个字段名，一个结果值，显示上只有一列两行，实际要复制的时候必须复制一列3行，即把结果值下面的那个单元格也一起复制，否则复制过去的就不是查询表，右键点击就不会有“编辑查询”菜单（经本人测试，EXCEL2007/2010版本创建的SQL查询表不存在此问题）。
●调试复杂SQL语句技巧（比如用到嵌套查询或联合查询或子句较多）：
可以将SQL语句分割成多个独立的查询分语句或去掉一些复杂子句，然后逐一测试分语句或逐步添加子句，这样便于发现问题，或观察中间结果是否与自己的期望结果一致。
●注意检查源表字段的数据类型：
这个其实不叫技巧，但需要引起重视，尤其是出现古怪的结果的时候。
尽量采用规范的表格数据结构，同一个字段的数据类型要注意保持一致，否则可能出现意想不到的结果，超出你的期望。
4.SQL函数分类
EXCEL2003中的SQL函数主要有如下几类（共有88个）：
●聚合函数（也有称作“合计函数”、“聚集函数”等其他叫法的，我们主要注重学习用法。
）
●字符串相关函数
●日期时间函数
●数学函数
●测试函数
●流程控制函数
二、聚合函数
这一部分主要参考了《MicrosoftJetSQL语言参考》中的内容，并结合本人的测试示例和一些亲身体会来介绍。
聚合函数主要有：
●Sum函数
●Count函数
●Avg函数
●Min,Max函数
●First,Last函数
●StDev,StDevP函数
●Var和VarP函数
注意：
使用聚合函数的查询必须要有from子句。
5.Sum函数
返回值：
包含在指定查询字段中一组值的总计。
语法:
Sum（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL聚合函数）。
说明：
Sum函数会忽略包含Null字段的记录。
可以在一个查询表达式之中使用Sum函数。
你也可以在具备SQL特性的QueryDef对象中或在创建基于SQL查询的Recordset对象时使用这一表达式。
示例：
示例1-1：
查询各省份的发货数量小计：
select省份,sum（数量）as数量小计from[发货清单$]groupby省份
示例1-2：
统计发货总数量：
selectsum（数量）as总数量from[发货清单$]
示例1-3：
统计湖南省2009年发货总数量：
selectsum（数量）as2009年湖南发货总屏数from[发货清单$]where省份='湖南'and发货日期between#2009-1-1#and#2009-12-31#
注意：
在EXCEL2003中，上述示例1-2、示例1-3的返回值虽然都是一个，只有2个单元格（包括字段名称）有显示内容，但所在的SQL查询结果占用了3个单元格（包括第3行所在同列的单元格）。
此外，sum（）函数和后面介绍的count（）函数在排名等其他方面的高级应用，请大家参考Scarlett_88讲师的精华帖：
SQL&Excel结合经典剖析：
47期基础题呀，几多疑惑，几多玩味。
我们来细细品一品。
6.Count函数
返回值：
计算从查询返回的记录数。
语法：
Count（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）.您可以计算任何种类的数据，包含文本数据。
说明：
可以使用Count计算下一级查询的记录数。
例如，可以使用Count计算运往指定国家的货物订单数量。
虽然expr能执行一个字段上的计算，但Count只是简单计算记录的数量。
并不管记录中保存的是什么值。
Count函数不数带有Null字段的记录，除非expr是星号（*）通配符dadefWildcardCharacters.如果使用星号，Count将计算所有记录的总量，包括有Null的字段的记录。
Count（*）比Count（[ColumnName]）快得多。
不要将星号放在引号（''）中。
示例：
示例2：
计算“发货清单”表中的省份个数：
：
SELECTcount（*）as省份个数FROM
（SELECTdistinct省份from[发货清单$]）
上例中的distinct关键字使得嵌套查询子句获得所有不重复的省份，然后通过外层查询中的count（*）计算省份个数。
如果expr标识多重字段，在至少有一个字段的值不为Null的情况下，Count函数只计算一个记录。
如果全部的指定字段为Null，没有记录会被计算。
使用（&）分隔字段名。
在查询表达式中可以使用Count。
你可以把这些表达式应用于具备SQL特性的QueryDef对象中或在创建基于SQL查询的Recordset时。
注意：
其他系统环境下的SQL中，有的支持count（distinct/all列名）的样式，EXCEL2003中的外部导入数据就是使用的JETDB4.0SQL，并不支持count（distinct/all列名）的用法，只支持count（列名或列名组合表达式）或count（*）的样式。
7.Avg函数
返回值：
计算包含在特定查询字段中的一组数值的算术平均值。
语法：
Avg（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算平均值的数据；或者标识一个表达式，它用该字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）。
说明：
使用Avg计算的平均值是算术平均值（将全部值的总和除以值的数目）。
注意：
Avg函数在计算中不计任何Null字段。
示例：
示例3-1：
查询各省份，各类型的平均数量：
select省份,类型,avg（数量）as平均数量from[发货清单$]groupby省份,类型
示例3-2：
统计2007年工程平均数量大于3的各省工程平均数量，并按平均数量降序排列：
select省份,avg（数量）as平均数量from[发货清单$]whereyear（发货日期）=2007groupby省份having（avg（数量）>3）orderbyavg（数量）desc
8.Min和Max函数
返回值：
在查询时从一组指定字段的值中返回最小或最大值。
语法:
Min（expr）
Max（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）。
说明:
可以使用Min及Max来计算字段中的最小及最大值，该字段是基于指定的合计或分组的。
例如，可以使用这些函数返回最低和最高的货运成本。
如果没有指定合计，则使用整个表。
示例：
示例4-1：
获得“发货清单”表各省份中的最大发货量：
selectmax（发货量）as各省份中的最大发货量from
（select省份,sum（数量）as发货量from[发货清单$]groupby省份）
示例4-2：
获得“发货清单”表各省份中的最小发货量：
selectmin（发货量）as各省份中的最小发货量from
（select省份,sum（数量）as发货量from[发货清单$]groupby省份）
9.First和Last函数
返回值：
在查询所返回的结果集中，第一个记录或最后一个记录所返回的字段值。
语法：
First（expr）
Last（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）。
说明：
First和Last函数与MoveFirst和MoveLast法在DAO记录集合对象中是相近的。
它们只简单返回查询结果集中的第一个或最后一个记录的指定字段值。
因为通常返回的记录没有一定的顺序，所以这些函数返回的记录会任意地排列。
注意：
经本人测试，EXCEL-SQL系统中，带有first或last函数的查询语句，即使查询语句里包含一个ORDERBY子句也不起作用，系统还是按原有默认顺序的记录返回第一行或最后一行结果，对此有异议的朋友可以发帖讨论。
示例：
示例5-1：
提取2008年第一笔发货记录的合同号及发货数量：
selectfirst（合同号）as2008年第一笔发货记录的合同号,first（数量）as发货数量from[发货清单$]whereyear（发货日期）=2008
示例5-2：
提取最后发货记录的合同号及发货数量：
selectlast（合同号）as最后发货的合同号,last（数量）as发货数量from[发货清单$]
【特别说明】下面提到的四个聚合函数（StDev、StDevP、Var、VarP函数）本人均未测试，文中有关这四个函数的内容完全摘自于《MicrosoftJetSQL语言参考》中的介绍，这里也就没有提供实测示例。
请对此有兴趣的朋友自行研究，当然也欢迎分享你的心得体会和研究成果。
10.StDev和StDevP函数
返回值：
返回总体或总体样本的标准偏差的估计值，此估计值用包含在一个查询的指定字段中的一组值来表示。
语法：
StDev（expr）
StDevP（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）。
说明:
StDevP函数计算总体，而StDev函数则计算总体样本。
如果下一级查询中包含两个以下的记录（或没有记录，对于StDevP函数），这两个函数将返回一个Null值（表示不能计算标准偏差）。
11.Var和VarP函数
返回值：
返回一个总体或总体样本的方差的估计值，此估计值用包含在指定查询字段中的一组值来表示。
语法：
Var（expr）
VarP（expr）
其中expr代表一个字符串表达式，它或者标识一个字段，该字段包含要计算的数据；或者是一个表达式，它使用此字段中的数据来执行计算。
expr中的运算对象可能包括一个表字段名，一个常数或一个函数（可能是内在的，也可能是用户自定义的，但不是SQL合计函数）。
说明:
使用VarP函数计算总体，而Var函数计算总体样本。
如果下一级查询中包含两个以下的记录，Var及VarP函数将返回一个Null值，表示不能计算方差。
三、字符串相关函数
（一）字符串查找函数
1.Instr函数
返回值：
返回变量型（长型）Variant（Long），指定一字符串在另一字符串中最先出现的位置。
语法：
InStr（[start,]string1,string2[,compare]）
由于EXCLEOLEDB中的SQL没有replace类似的函数，所以Instr函数的使用在某些场合显得尤为重要，所以这里就对该函数的各参数做详细说明：
start可选。
数值表达式，设置每次搜索的开始位置。
如果省略，则从第一个字符位置开始搜索。
如果start包含Null，将产生错误。
如果指定compare参数，则必须指定start参数。
string1必选。
要在其中进行搜索的字符串表达式。
string2必选。
被搜索的字符串表达式。
compare可选。
指定字符串比较类型。
如果compare为Null，将产生错误。
如果省略compare，则比较类型由OptionCompare设置决定。
请指定有效的LCID（区域设置ID），以在比较中使用区域设置特定的规则。
compare参数设置如下：
常量值说明
vbUseCompareOption-1使用OptionCompare语句的设置执行比较。
vbBinaryCompare0执行二进制比较。
vbTextCompare1执行文本比较。
（此时不区分字母的大小写）
vbDatabaseCompare2MicrosoftOfficeAccess2007专用。
根据数据库中的信息执行比较。
selectInstr（"XXpXXpXXPXXP","P"）asP的位置------返回P的位置值9。
注意：
在参数compare缺省的情况下，instr（）函数区分大小写。
示例：
Instr（4,"XXpXXpXXPXXP","P"）------返回6；
Instr（1,"XXpXXpXXPXXP","w"）------返回0；
注意：
上述两个示例都没有from子句。
其实，根据我的学习体会，除了聚集函数必须要有from子句之外，本文中提到的其他各类SQL函数都可以不需要from子句，要注意的是，此时该SQL语句查询结果返回值只能是一个。
select公司名称,trim（mid（电话,instr（电话,'）'）+1））as号码from[供应商$]
这是一个相对复杂的应用，根据供应商表中原有电话列的“（区号）号码”的形式，提取各公司的联系电话号码（不要括号和区号，并去掉空格）。
2.InStrB函数
返回值：
作用于包含在字符串中的字节数据。
所以InStrB返回的是字节位置，而不是字符位置。
（二）字符串转换函数
1.CBool（expression）转换为Boolean型
2.CByte（expression）转换为Byte型
3.CCur（expression）转换为Currency型
4.CDate（expression）转换为Date型
5.CDbl（expression）转换为Double型
6.CDec（expression）转换为Decemal型
7.CInt（expression）转换为Integer型
8.CLng（expressio
升级会员 