分组排序法取出连续天数

1、row_number() over()排序功能:

(1) row_number() over()分组排序功能:

     在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by  order by 的执行。

partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。

例如:employee,根据部门分组排序。

复制代码

SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee   
--------------------------------------  
000010  A00 152750  1  
000110  A00 66500   2  
000120  A00 49250   3  
200010  A00 46500   4  
200120  A00 39250   5  
000020  B01 94250   1  
000030  C01 98250   1  
000130  C01 73800   2  

复制代码

(2)对查询结果进行排序:(无分组)

复制代码

SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (ORDER BY salary desc) rank FROM employee   
--------------------------------------  
000010  A00 152750  1  
000030  C01 98250   2  
000070  D21 96170   3  
000020  B01 94250   4  
000090  E11 89750   5  
000100  E21 86150   6  
000050  E01 80175   7  
000130  C01 73800   8  
000060  D11 72250   9  

复制代码

row_number() over()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).

 

2、rank() over()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).

复制代码

select workdept,salary,rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept;  
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   3  

复制代码

3、dense_rank() over()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .

复制代码

select workdept,salary,dense_rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept;  
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   2  
C01 98250   3  

复制代码

使用ROW_NUMBER删除重复数据 
---假设表TAB中有a,b,c三列,可以使用下列语句删除a,b,c都相同的重复行。

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   
WHERE ROW_NO>1  
展开阅读全文

连续日期天数

08-23

求连续日期天数rnrn求连续日期天数,同时,到计数到8后,rn又要重1开始计数.有日期空缺的也从1重新开始计算rnrn数据如下:(同一日期可以有多条记录,有多条时只算一条即可)rndtrn2011-08-01rn2011-08-02rn2011-08-02rn2011-08-03rn2011-08-04rn2011-08-05rn2011-08-06rn2011-08-07rn2011-08-08rn2011-08-08rn2011-08-09rn2011-08-10rn2011-08-12rn2011-08-13rn2011-08-14rn2011-08-15rn2011-08-16rn2011-08-17rn2011-08-18rn2011-08-19rn2011-08-20rn2011-08-21rnrn结果如下:rndt/天数rn2011-08-01 1rn2011-08-02 2rn2011-08-02 2rn2011-08-03 3rn2011-08-04 4rn2011-08-05 5rn2011-08-06 6rn2011-08-07 7rn2011-08-08 8rn2011-08-08 8rn2011-08-09 1--计数到8后,下一个日期从1开始计数rn2011-08-10 2rn2011-08-12 1--中间没有11号资料,重新计数rn2011-08-13 2rn2011-08-14 3rn2011-08-15 4rn2011-08-16 5rn2011-08-17 6rn2011-08-18 7rn2011-08-19 8rn2011-08-20 1--计数到8后,下一个日期从1开始计数rn2011-08-21 2rnrn示例数据rn[code=SQL]rncreate table #t(id int identity(1,1),dt datetime )rninsert into #t(dt)rnselect '2011-08-01'rnunion allrnselect '2011-08-02'rnunion allrnselect '2011-08-02'rnunion allrnselect '2011-08-03'rnunion allrnselect '2011-08-04'rnunion allrnselect '2011-08-05'rnunion allrnselect '2011-08-06'rnunion allrnselect '2011-08-07'rnunion allrnselect '2011-08-08'rnunion allrnselect '2011-08-08'rnunion allrnselect '2011-08-09'rnunion allrnselect '2011-08-10'rnunion allrnselect '2011-08-12'rnunion allrnselect '2011-08-13'rnunion allrnselect '2011-08-14'rnunion allrnselect '2011-08-15'rnunion allrnselect '2011-08-16'rnunion allrnselect '2011-08-17'rnunion allrnselect '2011-08-18'rnunion allrnselect '2011-08-19'rnunion allrnselect '2011-08-20'rnunion allrnselect '2011-08-21'rn[/code] 论坛

sql连续天数问题

04-02

create table #loginlog(logintime datetime,u_id int)rnrninsert into #loginlog select '2011-12-16',907rninsert into #loginlog select '2011-12-17',907rninsert into #loginlog select '2011-12-18',907rninsert into #loginlog select '2011-12-14',1100rninsert into #loginlog select '2011-12-15',1100rninsert into #loginlog select '2011-12-16',1100rninsert into #loginlog select '2011-12-13',1200rninsert into #loginlog select '2011-12-15',1200rninsert into #loginlog select '2011-12-16',1200rninsert into #loginlog select '2011-12-17',1200rninsert into #loginlog select '2011-12-18',1200rninsert into #loginlog select '2011-12-19',1200rninsert into #loginlog select '2011-12-20',1200rninsert into #loginlog select '2011-12-21',1200rninsert into #loginlog select '2011-12-21',1200rninsert into #loginlog select '2011-12-22',1200rninsert into #loginlog select '2011-12-23',1200rninsert into #loginlog select '2011-12-24',1200rninsert into #loginlog select '2011-12-25',1200rninsert into #loginlog select '2011-12-26',1200rninsert into #loginlog select '2011-12-27',1200rninsert into #loginlog select '2011-12-28',1200rngorn--同一个帐号,连续登陆的最大天数rn;WITH cte ASrn(rn SELECT b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,logintime),112)AS dn,COUNT(1) AS countsrn FROM rn (rn SELECT u_id,ROW_NUMBER()OVER(PARTITION BY u_id ORDER BY logintime) AS sn,logintimern FROM #loginlogrn )AS brn GROUP BY b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,b.logintime),112)rn)rnSELECT cte.u_id,MAX(counts) as counts FROM cte GROUP BY cte.u_idrnrn这是查询账号连续几天登陆次数的rn其中结果账号1200:显示9rn事实上账号1200:应该显示是14rn其中rninsert into #loginlog select '2011-12-21',1200rninsert into #loginlog select '2011-12-21',1200rn有2条,是该用用户这天登陆了2次,如果'2011-12-21',1200数据只有一条的话,那结果就是正确的显示14rn请问该怎么改才能支持有每天不断重复登陆的,麻烦大家了!! 论坛

没有更多推荐了,返回首页