table a
id name
1 tom
2 Jery
3 Jack
4 Mary
select id,name ,返回排序后的位数 from a order by name desc
求返回排序后的
如 Mary 为第一位则返回 1
Sql代码
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
Sql代码
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 '
'
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=2 then '
'
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=3 then '
'
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=4 then '
'
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=5 then '
'
else '
' 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
作者“logout.jsp”