2005 than in 2000 added a few functions, is the ROW_NUMBER (), rank () dense_rank, (), ntile (takes) The following examples briefly explain.
create table gg (sname varchar (10), sort varchar (10), num int)
go
insert into gg
select ‘White peony root’, ‘root vegetables’, 55
union all
select ‘Pinellia law’, ‘tubers’, 78
union all
select ‘Bupleurum’, ‘tubers’, 60
union all
select ‘Chuanxiong’, ‘tubers’, 99
union all
select ‘Days censer’, ‘grass’, 68
union all
select ‘Rushes’, ‘grass’, 55
union all
select ‘Solanum nigrum’, ‘grass’, 60
union all
select ‘Iwami wear’, ‘grass’, 60
union all
select ‘Nepenthes’, ‘grass’, 70
union all
select ‘Motherwort’, ‘grass’, 86
union all
select ‘Lentils’, ‘fruits’, 86
union all
select ‘Tsaoko’, ‘fruits’, 70
union all
select ‘Rosa laevigata’, ‘fruits’, 55
union all
select ‘Ligustrum lucidum’, ‘fruits’, 94
union all
select ‘Panda Hai’, ‘fruits’, 66
union all
select ‘Mulberry’, ‘fruits’, 78
select sname, sort, num,
row_number () over (order by num) AS rownum,
RANK () over (order by num) as ranknum
dense_rank () over (order by num) AS dersenum
ntile (3) over (order by num) as ntilenum
from gg
– Results
– ROW_NUMBER () is a small to large num one by one rank, not side by side, in continuous
– RANK () num Ascending rank one by one, side by side, the ranking is not continuous
– DENSE_RANK () one by one num Ascending ranked tied for fourth consecutive
– Ntile (takes) num small to big into groups one by one ranking tied for fourth consecutive
sname sort num rownum ranknum dersenum ntilenum
————————————————– ——————————–
Root of herbaceous peony Tubers 55 1 1 1 1
Rush Grasses 55 2 1 1 1
Jinyingzi Fruits 55 3 1 1 1
Solanum nigrum Grasses 60 4 4 2 1
Stone see through Grasses 60 5 4 2 1
Bupleurum Tubers 60 6 4 2 1
Panda Hai Fruits 66 7 7 3 2
Days censer Grasses 68 8 8 4 2
Tsaoko Fruits 70 9 9 5 2
Nepenthes Grasses 70 10 9 5 2
France Pinellia Tubers 78 11 11 6 2
Mulberry Fruits 78 12 11 6 3
Motherwort Grasses 86 13 13 7 3
Hyacinth bean Fruits 86 14 13 7 3
Ligustrum lucidum Fruits 94 15 15 8 3
Chuanxiong Tubers 99 16 16 9 3
(16 rows affected)
select sname, sort, num,
row_number () over (partition by sort order by num) AS rownum,
RANK () over (partition by sort order by num) as ranknum
dense_rank () over (partition by sort order by num) AS dersenum
ntile (3) over (partition by sort order by num) as ntilenum
from gg
– Results
At this point add the partition by sort by category classified ntile (takes 3) means that the force divided into three groups.
sname sort num rownum ranknum dersenum ntilenum
—————— ————————————————– ————–
Rush Grasses 55 1 1 1 1
Solanum nigrum Grasses 60 2 2 2 1
Stone see through Grasses 60 3 2 2 2
Days censer Grasses 68 4 4 3 2
Nepenthes Grasses 70 5 5 4 3
Motherwort Grasses 86 6 6 5 3
Root of herbaceous peony Tubers 55 1 1 1 1
Bupleurum Tubers 60 2 2 2 1
France Pinellia Tubers 78 3 3 3 2
Chuanxiong Tubers 99 4 4 4 3
Jinyingzi Fruits 55 1 1 1 1
Panda Hai Fruits 66 2 2 2 1
Tsaoko Fruits 70 3 3 3 2
Mulberry Fruits 78 4 4 4 2
Hyacinth bean Fruits 86 5 5 5 3
Ligustrum lucidum Fruits 94 6 6 6 3
(16 rows affected)
The following SQL 2000 to achieve, compared to 2005 to more trouble.
– ROW_NUMBER in SQL 2000 in the implementation of
– The use of temporary tables and Identity (function)
select sname, num, identity (int, 1, 1) as rownumber
INTO # tem
from gg
order by num
select sname, num, rownumber
from # tem
drop table # tem
go
– RANK achieve in sql 2000
select sname, num,
(Select count (1) + 1 from gg where num <g. num) as ranknum
from gg g
order by num
go
– The in sql DENSE_RANK of the 2000 in the realization of
select num, identity (int, 1, 1) as densenum
into # t
from gg
group by num
order by num
select r. sname, r. num, t. densenum
from gg r join # tt
on r. num = t. num
order by num
drop table # t