标签归档:row_number应用

mssql sqlserver sql数据分组后,取分组后最新一条记录的方法分享


摘要:
下文讲述对通过举例的方式分享,“sql分组后,取最新一条记录的方法”,如下所示:
实验环境:sql server 2008 R2


例:
取销售订单中,每个客户最近的一条订单记录。
实验思路:
使用row_number() 对数据进行群组,群组内按照订单日期倒序排序,并生成新的群组内编号

   create table [maomao365](orderNo varchar(30),custName nvarchar(30),
     qty int, amount decimal(18,2),
     orderDate datetime)
    go
    ---生成订单数据
    insert into [maomao365](orderNo,custName,qty,amount,orderDate)
     values
     ('001','深圳A集团',8,10,'2018-1-1'),
     ('002','深圳A集团',7,1008,'2018-1-2'),
     ('003','深圳C集团',6,100,'2018-1-3'),
     ('004','深圳C集团',12,1090,'2018-1-4'),
     ('005','深圳B集团',5,108,'2018-1-5'),
     ('006','深圳B集团',16,190,'2018-1-6'),
     ('007','深圳D集团',10,90,'2018-1-7'),
     ('008','深圳D集团',4,40,'2018-1-8'),
     ('009','深圳E集团',6,60,'2018-1-9')
     go
     ---显示每个客户最新一条订单记录
     select * from 
     (
       select row_number() over(partition by [custName] order by [orderDate] desc ) as keyId,* from [maomao365]   
     )
     as t 
     where t.keyId =1 
    go
    
   go 
   truncate table [maomao365]
   drop       table [maomao365]

mssql sqlserver 获取数据表中某一列的”次大值或次小值”的方法分享


摘要:
下文获取群组数据后的第n大的数或倒数第n小的数,如下所示:
实验环境:sql server 2008 R2


在使用sql脚本进行数据查询时,我们通常可用使用max min函数获取指定列的最大值和最小值,但当我们想获取第二大的值时,没有现成的函数使用,此时我们需使开窗函数row_number对群组后的数据进行排序,然后获取相应的值
例:

 
     create table #maomao([sort] nvarchar(30),[qty] int)
       insert into #maomao
       select N'香水类',9 union all
       select N'香水类',18 union all
       select N'香水类',20 union all
       select N'导航类',16 union all
       select N'导航类',13 union all
       select N'导航类',17 union all
       select N'导航类',22
       go
        ---获取每一类中 qty排名第二的值
       ;WITH tmp AS (
          select *,ROW_NUMBER()OVER(PARTITION BY sort ORDER BY qty DESC) rank from #maomao
         )
        SELECT * FROM tmp WHERE tmp.rank=2

       go
  
       truncate table #maomao
       drop     table #maomao    
   

mssql sqlserver 使用sql脚本获取群组后,按时间排序(asc)第一条数据的方法分享


摘要:
下文讲述使用sql脚本,获取群组后记录的第一条数据


业务场景说明:
学校教务处要求统计:
每次作业,最早提交的学生名单
下文通过举例的方式,记录此次脚本编写方法,方便以后备查,如下所示:

实现思路:
使用开窗函数,对数据进行分组并按照提交时间进行排序后生成新的组内编号,如下所示:

  /*
over开窗函数中 partition by分组
order by 排序
*/
create table test(keyId int identity,
keChengName nvarchar(20),
name nvarchar(20),
inDate datetime)
go

insert into test(keChengName,name,inDate)
values('语文','猫猫','2018-9-1 10:00:00'),
('语文','maomao365','2018-9-1 13:00:00'),
('数学','sql教程','2018-9-1 8:00:00'),
('数学','sql博客教程','2018-9-1 9:00:00'),
('数学','其它','2018-9-1 8:10:00')

select * from 
(
select *,
row_number() over(partition by keChengName 
                  order by inDate asc ) 
                  as newKeyId 
 from test 
) as t where t.newKeyId =1 


go
truncate table test
drop table test 
mssql_sqlserver_row_number_获取分组数据排序第一条的方法分享

mssql_sqlserver_row_number_获取分组数据排序第一条的方法分享