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