
EXCEL2003 OLEDB查询下的SQL函数整理(whsfhwm).doc
29页EXCEL2003 OLE DB环境下的SQL函数介绍目录一、 前言 31. 背景简介 32. 适用范围: 43. 技巧介绍 44. SQL函数分类 5二、 聚合函数 61. Sum 函数 62. Count 函数 83. Avg 函数 94. Min 和 Max 函数 105. First 和 Last 函数 116. StDev 和 StDevP 函数 127. Var 和 VarP 函数 13三、 字符串相关函数 13(一) 字符串查找函数 131. Instr函数 132. InStrB 函数 15(二) 字符串转换函数 151. CBool(expression) 152. CByte(expression) 153. CCur(expression) 154. CDate(expression) 155. CDbl(expression) 156. CDec(expression) 157. CInt(expression) 158. CLng(expression) 159. CSng(expression) 1510. CStr(expression) 1511. CVar(expression) 1512. Val(string) 1613. Str(number) 16(三) 字符串处理函数 171. Trim(string) 172. Ltrim(string) 173. Rtrim(string) 174. Len(string) 175. Left(string, x) 176. Right(string, x) 177. Mid(string, start,x) 178. Ucase(string) 189. Lcase(string) 1810. Space(x) 1811. Asc(string) 1812. AscB(string) 1813. AscW(string) 1814. Chr(charcode) 1815. ChrW(charcode as long) 1816. String(number,string) 1817. LeftB(String, Length As Long) 1818. RightB(String, Length As Long) 1919. MidB(String, Start As Long, [Length]) 1920. LenB(Expression) 1921. Format(expression[, format[, firstdayofweek[, firstweekofyear]]]) 1922. StrComp(string1, string2[, compare]) 2023. StrConv(string, conversion, LCID) 21四、 数学函数 221. Sin(X)、Cos(X)、Tan(X)、Atan(x) 222. Log(x) 223. Exp(x) 224. Abs(x) 返回绝对值。
225. Int(number)、Fix(number) 226. Sgn(number) 227. Sqr(number) 228. VarType(varname) 229. Rnd(x) 2210. ROUND(c,decimals) 22五、 测试函数 231. IsNumeric(x) 232. IsDate(x) 233. IsEmpty(x) 234. IsError(expression) 235. IsNull(expression) 236. IsObject(identifier) 23六、 日期时间函数 231. 日期加减:DateAdd函数 232. 计算两个日期的差DateDiff函数: 243. 提取日期中的部分内容DatePart函数: 244. 用独立的部件生成日期DateSerial函数: 265. 用字符串表达式生成日期:DateValue(stringexpression) 266. Now() 267. Date() 268. Time() 269. Timer() 2610. TimeSerial(hour, minute, second) 2611. DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) 2712. Second(time) 2713. Minute(time) 2714. Hour(time) 2715. Day(date) 2716. Month(date) 2717. Year(date) 2718. Weekday(date, [firstdayofweek]) 27七、 流程控制函数 271. Iif()和Switch() 27八、 结束语 28一、 前言1. 背景简介一直想对EXCEL 2003中SQL语句里的可用函数进行专门的归纳整理,今天终于与大家见面了。
虽然目前EXCEL 2007/2010使用者越来越多,但无疑EXCEL 2003依然有着大量的使用人群,所以我才觉得很有必要对此进行整理,方便SQL初学者,希望他们能够少走一些弯路,节约一点时间来学习更多的精彩内容相信对于使用高版本的朋友来说,本文也是有一定的借鉴作用的构成本文的内容,有相当部分是来自于EH培训中心SQL初级培训班的培训过程,有引自EH技术论坛的内容,也有来自微软提供的帮助文档,当然还有本人自己钻研的一点点心得体会,所以这里主要是一种归纳整理由于本人水平所限,学习SQL时间也不长,所以有许多地方理解还比较肤浅,也难免有所疏漏或谬误,欢迎大家批评指正,或给与补充本文中,如果没有特别说明,则所提到的SQL函数都是已经经过本人在EXCEL2003中OLE DB环境下测试并通过的;文中提到的示例语句一般使用蓝色字体,由于示例太多的话,将会让EXCEL文件过于庞大,故只在EXCEL文件“EXCEL-OLE DB之SQL函数示例(whsfhwm整理).xls”里提供了一部分实例,方便大家进行测试,文中出现的示例,而上述EXCEL文件中又没有的,完全可以自己做测试另外,由于SQL语句的语法对于大小写不敏感,所以本文中并没有对SQL语句的书写进行严格规范,这一点请大家多多包涵。
2. 适用环境:本文介绍的内容适用的环境为:EXCEL 2003 OLE-DB环境下的SQL查询本文内容重点在于介绍SQL函数,而非SQL语句3. 技巧介绍介绍SQL函数之前,先介绍一下本人在测试SQL语句中的几个小技巧:l 编辑技巧:(虽然不是本人首创,但接触SQL多了,自然而然就需要这样做了)使用记事本或其他类似小巧的编辑工具来编辑SQL语句通常,你会感觉到编辑查询对话框中进行编辑SQL语句实在是不方便,因此推荐在记事本中进行编辑,好处有两点:一是可以方便的将数据源的标题行复制过来,然后用替换的方法将字段名称之间转化为“,”,这个是SQL语句语法的要求,这样复制过来的字段名称还可以避免自己录入出错的可能性;二是可以防止在所书写SQL语句有错误的情况下丢失刚才的语句,起到临时书写备份的作用,便于自己对错误语句的检查l 首次创建查询表技巧:第一次创建导入外部数据的查询表时,不得不通过菜单-导入外部数据-导入数据-选择数据源文件-出现“编辑OLE DB查询”对话框时,此时一般就不要匆忙将自己书写的SQL查询语句录入到命令文本中,而是采用默认的方式生成查询表,这样做的好处就是避免自己所写的SQL语句有误,不产生任何结果,然后下一次操作又要重复上面的步骤,效率显然较低;所以建议采用默认方式,然后再通过右键快捷菜单打开编辑查询对话框进行修改测试。
l 复制SQL查询表技巧:针对同一数据源创建多个SQL查询表时,第二个及以后的查询表可以不用通过菜单操作,而直接把第一个SQL查询表的全部区域进行复制(显然比通过菜单操作要快得多),然后修改SQL语句即可注意:当你的被复制的SQL查询表只返回一个值时,也就是只有一个字段名,一个结果值,显示上只有一列两行,实际要复制的时候必须复制一列3行,即把结果值下面的那个单元格也一起复制,否则复制过去的就不是查询表,右键点击就不会有“编辑查询”菜单(经本人测试,EXCEL2007/2010版本创建的SQL查询表不存在此问题)l 调试复杂SQL语句技巧(比如用到嵌套查询或联合查询或子句较多):可以将SQL语句分割成多个独立的查询分语句或去掉一些复杂子句,然后逐一测试分语句或逐步添加子句,这样便于发现问题,或观察中间结果是否与自己的期望结果一致l 注意检查源表字段的数据类型:这个其实不叫技巧,但需要引起重视,尤其是出现古怪的结果的时候尽量采用规范的表格数据结构,同一个字段的数据类型要注意保持一致,否则可能出现意想不到的结果,超出你的期望4. SQL函数分类EXCEL2003中的SQL函数主要有如下几类(共有88个):l 聚合函数(也有称作“合计函数”、“聚集函数”等其他叫法的,我们主要注重学习用法。
l 字符串相关函数l 日期时间函数l 数学函数l 测试函数l 流程控制函数二、 聚合函数这一部分主要参考了《Microsoft Jet SQL语言参考》中的内容,并结合本人的测试示例和一些亲身体会来介绍聚合函数主要有:l Sum 函数l Count 函数l Avg 函数l Min, Max 函数l First, Last 函数l StDev, StDevP 函数l Var 和 VarP 函数注意:使用聚合函数的查询必须要有from子句1. Sum 函数 返回值:包含在指定查询字段中一组值的总计语法: Sum(expr)其中 expr 代表一个字符串表达式,它或者标识一个字段,该字段包含要计算的数据;或者是一个表达式,它使用此字段中的数据来执行计算 expr中的运算对象可能包括一个表字段名,一个常数或一个函数(可能是内在的,也可能是用户自定义的,但不是 SQL 聚合函数)说明:Sum 函数会忽略包含 Null 字段的记录可以在一个查询表达式之中使用 Sum 函数你也可以在具备 SQL 特性的 QueryDef 对象中或在创建基于SQL查询的 Recordset 对象时使用这一表达式示例:示例1-1:查询各省份的发货数量小计:select 省份,sum(数量) as 数量小计 from [发货清单$] group by 省份示例1-2:统计发货总数量: select sum(数量) as 总数量 from [发货清单$]示例1-3:统计湖南省2009年发货总数量:select sum(数量) as 2009年湖南发货总屏数 from [发货清单$] where 省份='湖南' and 发货日期 between #2009-1-1# and #2009-12-31#注意:在EXCEL2003中,上述示例1-2、示例1-3 的返回值虽然都是一个,只有2个单元格(包括字段名称)有显示内容,但所在的SQL查询结果占用了3个单元格(包括第3行所在同列的单元格)。
此外,sum()函数和后面介绍的count()函数在排名等其他方面的高级应用,请大家参考Scarlett_88讲师的精华帖:SQ。
