19、SQL Server / Oracle：分组排序取头条记录（搜索结果中的重复记录仅取第一条）

一、(SQL Server的临时表格式)

分组排序取头条记录这种用法在实际项目中极为有效，比如需要将同一个ID的所有记录找出来，按照时间降序后，取第一条记录（即时间最靠近当前的记录），组成一个表

--先按照UserID分组，然后按照时间排序，取每组的第一条记录
insert into #TResult (
      UserID,
      UserName,

       DateTime,
       ProductName
)
select UserID,
      UserName,

       DateTime,
       ProductName
from
  (select * ,row_number() over (partition by UserID order by DateTimedesc) rn
   from #TMiddleResult
   ) t
where rn = 1

二、

下面第一部分摘自文章：http://www.cnblogs.com/hfliyi/archive/2013/03/17/2964057.html，本人略作整理如下

使用ROW_NUMBER()，其语法如下：

（1）、row_number() over(order by column asc)先对列column按照升序，再为每条记录返回一个序列号：

select personalid,row_number() over(orderby personalid asc) rn from  neogoodsrule

（2）、row_number() over(partition by column1 orderby column2 asc) 先按照column1分组，再对分组后的数据进行以column2升序排列

select personalid,ct_smp_type,row_number()over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule

举例：sql代码如下

create table NEOGOODSRULE

(

 ID          NUMBER(22) not null,

 PERSONALID  NVARCHAR2(50),

 CT_SMP_TYPE NVARCHAR2(100)

)

tablespace VGSM

 pctfree 10

 initrans 1

 maxtrans 255

 storage

  (

   initial 64K

   minextents 1

   maxextents unlimited

  );

select * from (selectpersonalid,ct_smp_type,row_number() over(partition by personalid order byct_smp_type asc) rn from neogoodsrule )

where rn=1

三、

以下第二部分来自文章：http://www.linuxidc.com/Linux/2015-04/116349.htm

Oracle中rank() over, dense_rank(), row_number() 的区别

假设现在有一张学生表student，学生表中有姓名、分数、课程编号，现在我需要按照课程对学生的成绩进行排序。

select * from student

1. rank over ()可以实现对学生排名，特点是成绩相同的两名是并列，如下1 2 2 4 5

select name,
      course,
      rank() over(partition by course order by score desc) as rank
  from student;

2. dense_rank()和rank over()很像，但学生成绩并列后并不会空出并列所占的名次，如下1 2 2 3 4

select name,
      course,
      dense_rank() over(partition by course order by score desc) as rank
  from student;

3. row_number这个函数不需要考虑是否并列，那怕根据条件查询出来的数值相同也会进行连续排名

select name,
      course,
      row_number() over(partition by course order by score desc) as rank
  from student;

答疑：

1. partition by用于给结果集进行分区。

2. partition by和group by有何区别？

partition by只是将原始数据进行名次排列(记录数不变)

group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)

3. 使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。

可以这样： rank over(partition by course order by score desc nulls last)