Sql学习第七天——SQL关于row_number()over()

Sql学习第七天——SQL关于row_number()over()

 

Sql学习第六天——SQL巩固练习

http://www.2cto.com/database/201304/206332.html

 

今天用到了row_number()over()，现在就以知识点的形式总结一下，以便于以后的回顾。

 

语法：

 

ROW_NUMBER ( ) 

    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

 

通过语法可以看出 over里有两个参数， partition by 和 order by ，其中partition by是可以不写的，但是order by是必须有的。可能对order by 比较熟悉（就是按什么排序），但 partition by是什么意思呢？partition  的中文解释是：n. 划分，分开；[数] 分割；隔墙；隔离物 vt. [数] 分割；分隔；区分。让我们再结合下面的参数说明和实例很容易就理解它的含义了。

 

参数：

 

PARTITION BY value_expression

         将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 value_expression 指定对结果集进行分区所依据的列。

　　　 如果未指定 PARTITION BY，则此函数将查询结果集的 所有行视为单个组。

order_by_clause

         ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

返回值类型：

 

bigint（长整型）

 

以上是对row_number()over()的理论了解，现在开始用例子演示：

 

先建表（dbo.PeopleInfo）：

 

CREATE TABLE [dbo].[PeopleInfo](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [Gender] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [numb] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [phone] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,

    [FenShu] [int] NULL

) ON [PRIMARY]

 

向表中插入数据：

 

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李欢','男','3223','1365255',80)

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李欢','男','322123','1',90)

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李名','男','3213112352','13152',56)

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李名','女','32132312','13342563',60)

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('王华','女','3223','1365255',80)

 

查询出所有插入的数据：

 

select * from  dbo.PeopleInfo

 

结果如图：

 

 

例子一：只用order by 不用 partition by 的sql语句如下：

 

--不用partition by

select [name],gender,fenshu, row_number() over(order by fenshu desc) as num from dbo.PeopleInfo

 

结果如图：

 

 

例子二：用order by 也用 partition by 的sql语句如下：

 

select [name],gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from dbo.PeopleInfo

 

结果如图：

 

 

比较例子一和例子二的结果图很容易就明白partition by的用处了，以例子二为例就是先用partition by把性别【Gender】分成两个区一个男一个女，然后再用order by 把每个区里的分数【fenshu】从大到小排序。

 

-----------------------------------------------

 

练习题（用到了cte（with。。。as）知识点）：

 

--查找出不同性别中分数最高的学生

with temp as

(

select [name],gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from dbo.PeopleInfo

)

select * from temp where num = 1

 

结果如图：