你真的会玩SQL吗?表表达式,排名函数

这次讲的有些可能是经常用但不会注意到,所以来统一总结一下用法。

我们往往需要临时存储某些结果集。除了用临时表和表变量,还可以使用公用表表达式的方法。

 

表表达式

  • 期待单个值的地方可以使用标量子查询
  • 期待多个值的地方可以使用多值子查询
  • 在期待出现表的地方可用表值子查询表表达式

1.派生表

是从查询表达式派生出虚拟结果表的表表达式,派生表的存在范围只是外部查询。

使用形式:from 派生表 as 派生表列名

规则:

  • 所有列必须有名称
  • 列名必须唯一
  • 不允许使用order by(除非指定了top)

不同于标量和多值子查询,派生表不能是相关的,它必须是独立的。

2.公用表表达式(CTE)

非递归公用表表达式(CTE)是查询结果仅仅一次性返回一个结果集用于外部查询调用。

 

递归公用表达式

来引用他人的一个示例:

先建一张表栏目表如下,栏目Id,栏目名称,栏目的父栏目

现在使用CTE查询其每个栏目是第几层栏目的代码如下:

结果:

0表示顶级栏目。1就是1级栏目

 

排名函数

四个排名函数:

  1.row_number

  2.rank

  3.dense_rank

  4.ntile

排名函数order by子句是必需的。我们这里不讲定义,直接讲实例用法。

利用row_number生成连续行号

小的分组范围内排序通过PARTITION BY选项来重新排序,给数据分区或者数据区域唯一的递增序号

如:LastName以‘A’开头的作为第一组,在这个组内进行排序。以‘B’开头的作为第二组,在这个组内排序。以‘C’开头的作为第三组,在这个组内进行排序,如此等等

结果

假设LastName以‘A’开头的是男子组,这个组有共有三个人,Kim Abercrombie是冠军,Jay Adams是亚军,Nancy Anderson是季军。假设LastName以‘B’开头的是女子组,这个组只有一个人Bryan Baker,无论如何她都是冠军。等等如此类推。这样一眼就能看出他们的小组名次了。

RANK

果有同时撞线的情况发生应该怎么计名次呢?例如A第一个撞线,B和C同时第二个撞线,D第三个撞线,如果我们想把D的名次计为第4名应该怎么处理呢?就是说不计顺序名次,只计人数。这时就可以使用RANK函数了。

在order by子句中定义的列上,如果返回一行数据与另一行具有相同的值,rank函数将给这些行赋予相同的排名数值。在排名的过程中,