现数据库数据如下
-------------------------
times date
1 2002-01-11
2 2008-12-01
3 2003-12-22
4 1998-05-02
5 1998-12-03
6 1999-07-05
------------------------------我想按照时间升序把次数改为对应的1,2,3,4..... 请问怎么写?
-------------------------
times date
1 2002-01-11
2 2008-12-01
3 2003-12-22
4 1998-05-02
5 1998-12-03
6 1999-07-05
------------------------------我想按照时间升序把次数改为对应的1,2,3,4..... 请问怎么写?
(select count(1) from tb b where b.date <= a.date) as times,
a.date
from tb a
order by a.date
如果是日期从大到小
if object_id('tb') is not null
drop table tb
go
create table tb(times int, date datetime)
insert tb
select 1, '2002-01-11' union all
select 2, '2008-12-01' union all
select 3, '2003-12-22' union all
select 4, '1998-05-02' union all
select 5, '1998-12-03' union all
select 6, '1999-07-05'select rank() over (order by date desc) as id,date from tb
如果是日期从小到大
if object_id('tb') is not null
drop table tb
go
create table tb(times int, date datetime)
insert tb
select 1, '2002-01-11' union all
select 2, '2008-12-01' union all
select 3, '2003-12-22' union all
select 4, '1998-05-02' union all
select 5, '1998-12-03' union all
select 6, '1999-07-05'select rank() over (order by date) as id,date from tb
times date sid
1 2002-01-11 1
2 2008-12-01 1
3 2003-12-22 1
4 1998-05-02 1
5 1998-12-03 1
6 1999-07-05 1
1 2002-01-11 2
2 2008-12-01 2
3 2003-12-22 2
4 1998-05-02 2
5 1998-12-03 2
6 1999-07-05 2
测试数据if object_id('tb') is not null
drop table tb
go
create table tb([date] datetime,[sid] int)
insert into tb
SELECT '2002-01-11' ,'1' UNION ALL
SELECT '2008-12-01' ,'1' UNION ALL
SELECT '2003-12-22' ,'1' UNION ALL
SELECT '1998-05-02' ,'1' UNION ALL
SELECT '1998-12-03' ,'1' UNION ALL
SELECT '1999-07-05' ,'1' UNION ALL
SELECT '2002-01-11' ,'2' UNION ALL
SELECT '2008-12-01' ,'2' UNION ALL
SELECT '2003-12-22' ,'2' UNION ALL
SELECT '1998-05-02' ,'2' UNION ALL
SELECT '1998-12-03' ,'2' UNION ALL
SELECT '1999-07-05' ,'2' select rank() over (order by sid,date) as times,date from tb结果times date
-------------------- -----------------------
1 1998-05-02 00:00:00.000
2 1998-12-03 00:00:00.000
3 1999-07-05 00:00:00.000
4 2002-01-11 00:00:00.000
5 2003-12-22 00:00:00.000
6 2008-12-01 00:00:00.000
7 1998-05-02 00:00:00.000
8 1998-12-03 00:00:00.000
9 1999-07-05 00:00:00.000
10 2002-01-11 00:00:00.000
11 2003-12-22 00:00:00.000
12 2008-12-01 00:00:00.000(12 row(s) affected)select rank() over (order by sid,date) as times,date from tb这个不对吧
select rank () over (PARTITION BY sid order by date) as times,date from tb
row_number() 与 rank()有何却别?谁能解释一下?
row_number()得到的结果是排名,如果排序列的值是一致的,得到的结果也一样。
比如:
name value
A 1
B 2
C 1
按照value值排序,
row_number()得到的结果是:
1 A 1
2 C 1
3 B 2
rank()得到的结果是:
1 A 1
1 C 1
2 B 2
select rank () over (PARTITION BY sid order by date) as times,date from tb
这两句我试了都可以。
UP,就是把后面一个RANK写成了row_number
INSERT INTO @tb
SELECT '2002-01-11' ,'1' UNION ALL
SELECT '2008-12-01' ,'1' UNION ALL
SELECT '2008-12-01' ,'1' UNION ALL
SELECT '1998-05-02' ,'1' UNION ALL
SELECT '1998-12-03' ,'1' UNION ALL
SELECT '1999-07-05' ,'1' UNION ALL
SELECT '2002-01-11' ,'2' UNION ALL
SELECT '2008-12-01' ,'2' UNION ALL
SELECT '2003-12-22' ,'2' UNION ALL
SELECT '1998-05-02' ,'2' UNION ALL
SELECT '2003-12-22' ,'2' UNION ALL
SELECT '1998-05-02' ,'2' SELECT ROW_NUMBER () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date ,sid FROM @tb
SELECT RANK () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date,sid FROM @tb
SELECT DENSE_RANK () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date,sid FROM @tb
--如果date 无重复值则以上三个是等价的
看看三个结果那个是你想要的吧:
SELECT ROW_NUMBER () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date ,sid FROM @tb
(12 row(s) affected)
times date sid
-------------------- ----------------------- -----------
1 2008-12-01 00:00:00.000 1
2 2008-12-01 00:00:00.000 1
3 2002-01-11 00:00:00.000 1
4 1999-07-05 00:00:00.000 1
5 1998-12-03 00:00:00.000 1
6 1998-05-02 00:00:00.000 1
1 2008-12-01 00:00:00.000 2
2 2003-12-22 00:00:00.000 2
3 2003-12-22 00:00:00.000 2
4 2002-01-11 00:00:00.000 2
5 1998-05-02 00:00:00.000 2
6 1998-05-02 00:00:00.000 2(12 row(s) affected)
SELECT RANK () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date,sid FROM @tb
times date sid
-------------------- ----------------------- -----------
1 2008-12-01 00:00:00.000 1
1 2008-12-01 00:00:00.000 1
3 2002-01-11 00:00:00.000 1
4 1999-07-05 00:00:00.000 1
5 1998-12-03 00:00:00.000 1
6 1998-05-02 00:00:00.000 1
1 2008-12-01 00:00:00.000 2
2 2003-12-22 00:00:00.000 2
2 2003-12-22 00:00:00.000 2
4 2002-01-11 00:00:00.000 2
5 1998-05-02 00:00:00.000 2
5 1998-05-02 00:00:00.000 2(12 row(s) affected)
SELECT DENSE_RANK () OVER (PARTITION BY sid ORDER BY date DESC) AS times,date,sid FROM @tb
times date sid
-------------------- ----------------------- -----------
1 2008-12-01 00:00:00.000 1
1 2008-12-01 00:00:00.000 1
2 2002-01-11 00:00:00.000 1
3 1999-07-05 00:00:00.000 1
4 1998-12-03 00:00:00.000 1
5 1998-05-02 00:00:00.000 1
1 2008-12-01 00:00:00.000 2
2 2003-12-22 00:00:00.000 2
2 2003-12-22 00:00:00.000 2
3 2002-01-11 00:00:00.000 2
4 1998-05-02 00:00:00.000 2
4 1998-05-02 00:00:00.000 2(12 row(s) affected)