sqlserver 排序后，标号

table a

id   name
1     tom
2     Jery
3     Jack
4     Mary


select id,name ,返回排序后的位数 from a order by name desc
求返回排序后的
如 Mary 为第一位则返回 1

 

 

select row_number() over (order by name desc) as rowNum,id,name from a order by name desc 

 相关

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

 

 

2011 9.7

SELECT DBFile,Subject=CASE WHEN LEN(DBSubject)>22 THEN SUBSTRING(DBSubject,1,20)+'...' ELSE DBSubject END,SUBSTRING(DBDate,1,10) AS ICreateDate
,TOPImg=case 
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=1 then '<img src="images/top1.jpg" border="0" />' 
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=2 then '<img src="images/top2.jpg" border="0" />' 
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=3 then '<img src="images/top3.jpg" border="0" />' 
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=4 then '<img src="images/top4.jpg" border="0" />' 
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=5 then '<img src="images/top5.jpg" border="0" />' 
else '<img src="images/top6.jpg" border="0" />' end
FROM tbInfo,tbCate,tbDatabase 
 WHERE DBID=IDBID AND ICate=CID AND 
 ICate LIKE '0110%' AND DBSend=1 ORDER BY DBSetTop asc,DBSort asc,DBDate desc