Excel2010SQL完全应用

1、第 5章SQL排序查询在日常工作中，通过查询后的结果通常需要按照某种方式排序，方可把结果应用到最终报告中。本章主要介绍SQL排序查询。排序查询在实际应用中的用途非常广泛，利用排序查询的技巧可以实现数据的顺序排列，也可以找出指定位置的记录，得到记录的排名等。5.1 单列数据排序本节通过4个案例介绍如何利用SQL中的ORDER BY子句对数据进行升序、降序排序以及排序的其他应用，帮助读者进一步加深对ORDER BY子句的理解，掌握常用的SQL排序方法。疑难80 如何提取某区域的销售记录并按销售额降序排列如图51所示为某公司各个区域的销售情况数据，现需要提取区域为“一区”的所有销售记录，并且需按照销

2、售额进行降序排列，应该如何操作？¢ 图51 销售记录表è 解决方案通过SQL中的数据排序ORDER BY子句将记录进行降序排列。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT * FROM 销售数据$ WHERE 区域="一区" ORDER BY 销售额 DE

3、SC单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到一区所有的销售记录和排序结果，如图52所示。¢ 图52 一区的销售记录及排序结果í 原理分析 利用ORDER BY子句对记录进行排序 本例利用SQL查询中ORDER BY子句对记录进行排序。ORDER BY子句语法如下：SELECT fieldlist FROM tableWHERE selectcriteria ORDER BY field ASC | DESC 参数fieldlist表示要查询的字段名称，以及任何字段名别名，table表示待查询的表

4、名，selectcriteria为条件表达式，field表示排序记录所依据的字段名称。关键字ASC和DESC表示进行升序或降序排序。在关键字缺省的情况下，SQL将默认按照指定字段进行升序排列，即“ORDER BY 销售额”等同于语句“ORDER BY 销售额 ASC”。本例中，要将销售记录按照销售额进行降序排列，则必须使用关键字DESC，同时还要对查询条件进行限定，限定在区域为“一区”的所有记录中，因而使用WHERE子句对条件进行设定。í 知识扩展 SQL查询机制步骤详解 利用SQL语句进行查询时，通常要对以下几个基本的子句进行设置。SELECT：指定选取的字段。FROM：指定选取的

5、表。WHERE：指定查询条件。ORDER BY：指定排序规则。当SQL执行时，对于上述4个子句的处理是有先后顺序的，以本例采用以下语句为例：SELECT 销售员,销售数量,销售额FROM 销售数据$ WHERE 区域="一区" ORDER BY 销售额 DESCSQL查询步骤如图53所示。¢ 图53 SQL查询步骤演示图疑难81 如何找出考试成绩总分前5名的同学每次考试后，班级内都要进行成绩排名。如图54所示，如何利用SQL快速找出总成绩排在前五名的同学？¢ 图54 期中考试成绩表è 解决方案通过SQL中的数据排序功能进行降序排列后，再利用TO

6、P谓语，即可轻松找出总成绩排在前五名的同学。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT TOP 5 姓名,(语文+数学+英语) AS 总分FROM 期中考试成绩$ ORDER BY 语文+数学+英语 DESC单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”

7、，然后单击“确定”按钮，即可得到考试成绩排在前五名的同学，如图55所示。¢ 图55 总分前5名的同学í 原理分析 利用TOP谓词查询前几条记录 本例的实现是在将数据降序排序的基础上利用TOP谓词取得前5条记录，即前5条分数最高的记录。TOP谓词可以返回指定范围内一定数量的记录，语法如下：TOP N PERCENT其中，PERCENT 关键字可以省略，当省略时，N代表要获取记录的数量；当加上PERCENT关键字后，N代表要获取记录的百分比。比如要获取前50%的记录，可以使用以下语句：TOP 50 PERCENT在未对数据进行任何排序的情况下，TOP谓词将按照原始数据的默认排序

8、取前N条记录。所以，在本例中，要实现查找考试成绩总分排在前5名的同学，必须将总分按照降序排序。本例排序过程示意图如图56所示。¢ 图56 查找总分排在前5名的同学排序过程示意í 知识扩展 TOP PERCENT保留记录规律 当记录百分比数量计算为小数时，SQL将按照向上取整的方式取得记录的数量。例如，记录数为11条，执行TOP 30 PERCENT语句时，前30%的记录数应为3.3，向上取整后，结果将返回4条记录。疑难82 如何查询百米赛跑项目第58名的运动员如图57所示为某次运动会百米赛跑比赛成绩表。如何使用SQL查询出第58名的运动员？¢ 图57 百米比赛成绩

9、è 解决方案由于比赛成绩用非标准时间表示，需要将成绩转换成可进行有效排序的时间序列或者数字，才能进行正确排序。在SQL中，对文本类型的数字进行四则运算后，即可将文本类型的数字转换成数字类型。利用该转换结果，即可进行对“比赛成绩”字段的排序。利用排序后的结果，要取第58名，可以先利用TOP谓词找出前4名的名单，然后利用条件表达式中的NOT IN运算符在排序结果中找出姓名不属于前4名的名单。在这些结果中再找出前4名，即可得到第58名的运动员。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，

10、再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT TOP 4 姓名,比赛成绩 FROM 百米比赛成绩$ WHERE 姓名 NOT IN (SELECT TOP 4 姓名 FROM 百米比赛成绩$ ORDER BY REPLACE(比赛成绩,"'",".")*1) ORDER BY REPLACE(比赛成绩,"'",".")*1单击“确定”按钮返回“导入

11、数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到百米赛跑项目第58名的运动员，结果如图58所示。¢ 图58 百米赛跑项目第58名的运动员í 原理分析 伪时间排序技巧 本例的实现首先要将非数字字段转换为数字字段。观察比赛成绩字段，要对其进行排序，应先比较单引号（）之前的数字，然后再比较单引号之后的数字。此比较规则同小数相似，因而可以利用REPLACE函数将所有的单引号替换成小数点，从而将比赛成绩字段转换为小数形式的文本数字，再将此结果乘以1之后，即可将字段转换为数字类型。从而可以利用下述表达式对比赛成绩字段进行转换：REPLACE

12、(比赛成绩,"'",".")*1利用该转换即可实现对“比赛成绩”字段进行排序，语句如下：SELECT 姓名,比赛成绩FROM 百米比赛成绩$ ORDER BY REPLACE(比赛成绩,"'",".")*1 查找排名中间的记录的技巧 上述语句执行的结果如图59所示。在本例中需要保留的是第58名的运动员。利用TOP谓词只能够查询前N条记录，无法利用TOP实现第NM条记录的查询，但是可以利用TOP N的结果对记录进行条件筛选。¢ 图59 百米赛跑排序利用TOP谓词可以查找前4名的运动员，利用该

13、查询结果结合WHERE子句中的IN运算符，可以在查询结果中排出前4名运动员。而第58名的运动员即在剩余的运动员中排前4名。因而，本例可以利用下列语句先查询前4名运动员：SELECT TOP 4 姓名FROM 百米比赛成绩$ ORDER BY REPLACE(比赛成绩,"'",".")*1然后利用WHERE的IN运算符排除前4名的运动员，语句如下：SELECT姓名,比赛成绩FROM 百米比赛成绩$ WHERE 姓名 NOT IN (SELECT TOP 4 姓名 FROM 百米比赛成绩$ ORDER BY REPLACE(比赛成绩,"&#

14、39;",".")*1)再将上述语句的查询结果添加排序和TOP谓词之后，即可实现第58名的运动员，最终得到所需要的语句。本例之所以加上TOP 4而不是TOP 8，正是因为SQL查询语句执行子句有先后顺序，排序子句在WHERE子句之后进行，若用花括号（）表示先后的优先级，可以将本例的语句表述如下：SELECT TOP 4 姓名,比赛成绩FROM 百米比赛成绩$ WHERE 姓名 NOT IN (SELECT TOP 4 姓名 FROM 百米比赛成绩$ ORDER BY REPLACE(比赛成绩,"'",".")*1)O

15、RDER BY REPLACE(比赛成绩,"'",".")*1í 知识扩展 TOP谓词出现重复记录的查询规律 可以发现，本例最终的查找结果为5条记录，而非所需要的4条记录，原因在于查询结果中存在比赛成绩相同的记录。当采用ORDER子句对数据进行排序后，再利用TOP谓词选择数据的前N条记录，只有当第N条记录在排序关键字恰有记录重复的情况下，会将这些重复的记录作为结果输出。如本例中，第8名恰有两名运动员的成绩相同，SQL查询将保留这两条记录，但若第7名出现成绩相同的情形时，SQL查询会保留该相同的记录，而不会再保留第8名的成绩。疑难83 如

16、何将考生姓名进行随机排序如图510所示为某次考试考生的名单。因考试需要，要对考生进行随机排序，如何利用SQL实现呢？¢ 图510 考生名单è 解决方案利用RND函数可以产生随机数，对该随机数字段进行排序，即可产生随机排序效果。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT * F

17、ROM 考生名单$ ORDER BY RND(学号)单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到随机排序的考生记录。步骤3在“数据”选项卡中单击“全部刷新”按钮，可以发现每单击一下，考生排列顺序就会不同，如图511所示。¢ 图511 随机排序结果í 原理分析 随机排序技巧 RND函数原本为Office VBA中的函数，在Excel SQL查询语句中，RND函数同样有效，其语法如下：Rnd(number)RND函数用于产生一个大于或等于0且小于1的伪随机数列，每调用RND函数一次，即可生成该数列中下一

18、个随机数。参数number为可省略的参数，其标志着随机数产生的种子。当该值大于0时，将产生不同的随机数列，number小于或等于0或者省略时，将产生固定的随机数。在Excel SQL查询运算中，number值可以取某个数字字段的值，以表示该字段的值作为随机数的种子而生成不同的随机数。本例中，可以利用“学号”字段作为随机数的种子，利用RND函数将每个学号作为种子，可以生成不同的随机数。因而可以以该随机数作为关键字进行排序，从而实现随机排序。î 注意当number采用的字段为0或者负数时，或者number为用户自定义的某个常数而非字段时，RND函数将会对所有的记录产生相同的随机数，而此时

19、则无法利用该随机数对记录进行随机排序。í 知识扩展 伪随机数概念 在Excel SQL中的RND函数和VBA中的RND函数有所不同：在VBA中的RND函数语法与Excel SQL中的RND函数语法一致，但是number参数的取值表示不同的含义。在VBA中，number为负数时，RND函数会将该number作为随机数种子进行随机数取值，这一点恰恰与Excel SQL中的RND函数相反。RND函数生成的是伪随机数。所谓伪随机数，看似为随机数，实质上在较长序列取值时还是有规律可循的。在大量的数据测试中，可能会得到相同的随机数列，从而导致多次随机排序出现相同的结果。但是在日常运用中，此伪随机

20、数完全可以满足一般用户的需要。5.2 多列数据排序本节通过3个案例介绍如何利用SQL中的ORDER BY子句对数据进行多字段排序的方法及相关应用。疑难84 如何将员工名单按照多级部门进行排序在公司的员工档案中，许多员工的数据顺序被打乱，在打印员工名单时就会显得比较凌乱。现在要将员工按照其所在部门根据一级部门、二级部门进行排序，将所属部门相同的员工记录放在一起，如图512所示。如何利用SQL实现呢？¢ 图512 员工名单排序结果示意è 解决方案通过SQL中的数据排序功能可以进行多字段的排序。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框

21、中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT * FROM 员工名单$ ORDER BY 一级部门,二级部门单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到按照多级部门进行排序后的员工名单。í 原理分析 多列排序技巧及规律 利用SQL的ORDER BY子句可以对多字段进行排序，其语法如下：ORDER

22、BY field1,field2其中，参数field1、field2即为要进行排序的字段。在对多字段进行排序时，用逗号将各字段隔开。在多字段排序中，排序将按照从左至右的顺序进行。即首先对field1进行排序，然后保留该结果中对field1字段的排序，再对field2字段进行排序，其排序规则类似于Excel工作表中的多列排序。对于本例的实现，要将所在部门相同的员工进行依次排列，首先就是对“一级部门”进行排序，然后对“二级部门”进行排序。因而可以利用SQL的多字段排序（多列数据排序），其排序过程如图513所示。¢ 图513 多字段排序过程示意图疑难85 如何从总分前10名中找出语文成绩最

23、差的3名同学如图514所示为某次考试的成绩单，现在要求从3门成绩总分前10名的同学中找出语文成绩最差的3名同学，该如何利用SQL实现呢？¢ 图514 成绩单è 解决方案本例的难点在于无法利用多字段排序直接得到所需要的结果，但可以通过WHERE条件子句的IN运算符先找出总分前10名的同学，然后利用排序结合TOP谓词找出这10名同学中语文成绩最差的3名同学。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复

24、选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT TOP 3 * FROM 成绩单$WHERE 姓名 IN (SELECT TOP 10 姓名 FROM 成绩单$ ORDER BY 语文+数学+英语 DESC)ORDER BY 语文 ASC单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到总分前10名中语文成绩最差的3名同学，如图515所示。¢ 图515 总分前10名中语文成绩最差的3名同学名单í 原理分析 在排序结果中查找指定记录

25、的技巧 ORDER BY子句可以对多字段的数据进行排序，但是对于本例，却无法直接通过多字段排序得到想要的结果，如下列语句：SELECT TOP 3 * FROM 成绩单$ ORDER BY 语文+数学+英语 DESC,语文 ASC原因在于：当对总分进行排序后，SQL查询会以总分排序结果作为基础，再对语文进行排序。只有当总分成绩相同时，SQL查询才会再次对总分成绩相同的记录进行语文字段的排序。但是其排名仍然按照总分从大到小进行排序，即便添加了语文关键字后，上述语句的最终结果仍然是总分前3名的同学，却无法得到总分前10名中语文成绩最差的3名同学。因而本例的实现必须通过一个中间结果，先利用TOP谓词

26、和排序语句找出总分前10名的同学名单。接着利用WHERE条件子句的IN运算符对“成绩单”中总分前10名的同学进行语文成绩的排序。因而，首先利用下列语句找出总分前10名的同学：SELECT TOP 10 姓名FROM 成绩单$ ORDER BY 语文+数学+英语 DESC然后利用WHERE条件子句的IN运算符将总分前10名的同学筛选出来，如下列语句：SELECT * FROM 成绩单$WHERE 姓名 IN (SELECT TOP 10 姓名 FROM 成绩单$ ORDER BY 语文+数学+英语 DESC)最后，在上述语句的基础上对“语文”字段进行升序排列后，利用TOP谓词取得记录的前3条，即

27、可得到总分前10名中语文成绩最差的3名同学。因而得到以下最终的查询语句：SELECT TOP 3 * FROM 成绩单$WHERE 姓名 IN (SELECT TOP 10 姓名 FROM 成绩单$ ORDER BY 语文+数学+英语 DESC)ORDER BY 语文 ASC本例SQL查询排序的过程如图516所示。¢ 图516 在总分前10名的同学中找出语文成绩最差的3名同学的查询过程疑难86 如何得到足球比赛小组赛的出线名单如图517所示为某次足球比赛小组赛成绩表。根据出线规则，该小组只能有两支球队出线。出线规则是积分靠前的两支球队出线；当积分相同时，净胜球（进球-失球）多的球队出

28、线；当净胜球相同时，进球多的球队出线。该如何用SQL实现呢？¢ 图517 小组赛成绩è 解决方案利用SQL的多字段排序对积分、净胜球、进球依次进行降序排序后，取前两条记录即为出线球队。í 操作方法步骤1通过OLE DB方法找到外部数据源，保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，再单击“属性”按钮，在弹出的“连接属性”对话框中勾选“打开文件时刷新数据”复选框，然后单击“定义”选项卡，清空“命令文本”框中原有的内容。步骤2在“命令文本”框中输入以下SQL连接语句：SELECT TOP 2 球队 AS 出线球队 FROM 小组赛成绩$ ORDER BY 积分 DESC,进球-失球 DESC,进球 DESC单击“确定”按钮返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可得到小组赛出线名单，结果如图518所示¢ 图518 小组赛出线名单í 原理分析 指定不同排序方法的多列排序 ORDER BY子句可以进行多字段的数据排序，并且可以在各字段之后加上关键字ASC或者D