聊下SQL在Excel中的排序技巧及扩展应用

数据分析学习社
数据分析学习社
数据分析学习社
348
文章
0
评论
2021-06-0909:48:00 评论 177 1812字
摘要

排序是我们使用Excel处理数据经常面对的问题,Excel甚至专门对此内置了【排序】功能。今天我们就来聊一下SQL如何对查询结果进行排序操作,也就是ORDER BY 语句

语法如下:

 

  •  
SELECT 字段名 FROM 表名 ORDER BY 字段名
该语句默认对记录进行升序排序,如果需要降序排序,可以使用关键字DESC:
  •  
SELECT 字段名 FROM 表名 ORDER BY 字段名 DESC
照例举个小例子。
如下图所示,工作表名称为"销售表",A列是月份,B列是产品,C列是生产量,D列是销售量,数据纯属虚拟,如有雷同——实属雷人。
聊下SQL在Excel中的排序技巧及扩展应用
如果我们需要查询该表月份和销售量两个字段的数据,并将销售量作升序排列,代码如下:
  •  
SELECT 月份,销售量 FROM [销售表$] ORDER BY 销售量
聊下SQL在Excel中的排序技巧及扩展应用
如果我们需要查询月份、生产量、销售量三个字段的数据,其中生产量优先排序,且降序排列,销售量升序排列,代码如下:
  •  
SELECT 月份,生产量,销售量 FROM [销售表$] ORDER BY 生产量 DESC,销售量 ASC
也就是说,ORDER BY语句中,优先排序的字段放在前面,不同字段可以指定不同的排序规则,如果没有指定排序规则,则默认为升序(ASC)排列。
代码结果如下:
聊下SQL在Excel中的排序技巧及扩展应用

 

……
依然使用上一节的例子,倘若我们需要对查询结果按月份进行降序排列,可能有些朋友代码会写成如下这般:
  •  
SELECT 月份,销售量 FROM [销售表$] ORDER BY 月份 DESC
但发现结果和我们想的并不一样。
聊下SQL在Excel中的排序技巧及扩展应用
月份的排列看起来完全是乱态的,既不是升序也不是降序。
上一章我们提过,SQL In Excel对简体中文排序的规则不走寻常路,既不按拼音字母排序,也不按笔画排序,而是可能和其它数据库一样,采用的Chinese_PRC针对大陆简体字UNICODE的排序规则,但和其它数据库所不同的是,我们没有修改该规则参数的权限,以达到拼音或笔划排序的目的……
不过,事实上,即便是使用Excel自带的排序功能(默认拼音字母排序),排序结果也并非是五四三二一月。
此时我们需要自定义排序规则,也就是使用SQL中的iif或者instr函数。
IF函数类似于工作表的IF函数,语法如下:
  •  
=IIF(条件表达式,真值结果,假值结果)
使用IIF自定义排序规则的SQL语句如下:
  •  
SELECT 月份,销售量 FROM [销售表$] ORDER BY IIF(月份="五月",1,IIF(月份="四月",2,IIF(月份="三月",3,IIF(月份="二月",4,IIF(月份="一月",5)))))
代码长的吓人?
但意思其实很简单。
……如果月份等于五月,就返回1,否则如果月份等于四月,就返回2,再否则如果月份等于三月……以此类推……最后ORDER BY语句按IIF返回的结果进行升序排序……
看了这个代码,是不是瞬间找回当年嵌套N层IF函数的青葱小岁月……
再说下INSTR函数。
INSTR函数有些类似于工作表函数FIND,查找一个字符串在另一个字符串中的位置,和FIND不同的是,当找不到相关值时,结果返回0,而非错误值。
  •  
  •  
INSTR(str, substr)返回substr在str中的位置,若不存在,则返回0。
使用INSTR函数自定义排序规则的SQL语句如下:
  •  
SELECT 月份,销售量 FROM [销售表$] ORDER BY INSTR("五月,四月,三月,二月,一月",月份)
查询结果如下:
聊下SQL在Excel中的排序技巧及扩展应用
INSTR函数的处理语句比起IIF函数来明显要简洁清爽的多。
——因此我们通常使用该函数处理自定义排序的问题。

 

……
提一个问题。如下。
如果使用SQL语句查询上述示例中,销售量前三名的月份和销售量,该怎么表述呢?
……
……
不要想的太复杂,其实很简单。
在原本对销量进行降序排序的结果上,提取前三行的记录就OK。
如何提取前3行记录呢?
可以使用关键字TOP
  •  
TOP n 指定检索结果集中的第n行前的记录。
完整SQL代码如下:
  •  
SELECT TOP 3 月份,销售量 FROM [销售表$] ORDER BY 销售量 DESC
查询结果如下:
聊下SQL在Excel中的排序技巧及扩展应用
除了支持返回指定数量的记录外,TOP语句还支持按百分比返回记录,这需要使用到关键字PERCENT。
比如,返回前2%条记录,语法如下▼
  •  
SELECT TOP 2 PERCENT * FROM [销售表$]  
而如果需要返回销量占比20%的记录,可以搭配ORDER BY语句▼
  •  
SELECT TOP 20 PERCENT * FROM [销售表$]  ORDER BY 销量

End.

作者:EH看见星光

转载如果涉及作品、版权其他问题请联系我们第一时间删除微信号:lovedata0520)

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: