create table #tb
(myid int)insert into #tb
select 1 union all
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 71,我好像哪里看到一个排序函数,结果:
序号 myid
1 1
1 1
2 2
3 4
4 6
5 7
2,select ROW_NUMBER() over (partition by myid order by myid)as 序号,myid from #tb order by myid结果为什么是:
序号 myid
1 1
2 1
1 2
1 4
1 6
1 7
select rank() over (order by myid)as 序号,myid from #tb order by myid2什么问题
partition by myid 是以myid 分组,这个函数其实是返回行数的函数...
如下:
ROW_NUMBER() OVER(PARTITION BY [分区列] ORDER BY [排名列] DESC)
ROW_NUMBER()函数用于为分区中的每一行返回一个序列号,每个分区的第一行从1开始。无分区则按行数累加。
如果没有:
partition by myid 的话 它是1 2 3 4 5 有几条记录累加几条的...
select myid,ROW_NUMBER() over (order by myid)as 序号
from #tb order by myid
---如下:
myid 序号
----------- --------------------
1 1
1 2
2 3
4 4
6 5
7 6(6 行受影响)
(myid int)insert into #tb
select 1 union all
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 7select ROW_NUMBER() over (order by myid)as 序号,myid
,RANK() over (order by myid)as 序号1
,DENSE_RANK() over (order by myid)as 序号2
from #tb order by myid--结果
序号 myid 序号1 序号2
1 1 1 1
2 1 1 1
3 2 3 2
4 4 4 3
5 6 5 4
6 7 6 5
(myid int)insert into #tb
select 1 union all
select 1 union all
select 2 union all
select 4 union all
select 6 union all
select 7
select *,(select count(distinct myid) from #tb where myid<=t.myid) as rn from #tb t
(所影响的行数为 6 行)myid rn
----------- -----------
1 1
1 1
2 2
4 3
6 4
7 5(所影响的行数为 6 行)