====== 先看个例子 ======
<code>
create table t(s number);
insert into t values(90);
insert into t values(50);
insert into t values(20);
commit;
</code>
<WRAP center round tip 60%>
问题：给出一个数，求出这个数在这个表中的排名
</WRAP>
<code>
假如：
给出100，返回1
给出90，返回1
给出80，返回2
给出40，返回3
给出10，返回4

提示：下文中的40是给出的数
1   oracle数据库下
select nvl(
(
    select rn
    from
    (
        select fir.*,rownum as rn from
        (select t.*,40 as value from t order by s desc) fir
    ) sec
    where value-s=
    (
        select min(value-s) as cut
        from
        (
            select fir.*,rownum as rn from
            (select t.*,40 as value from t order by s desc) fir
        )sec 
        where value-s>=0
    ) 
),--参数一
(select count(*)+1  from t)    --参数二
)  as  rank from dual

2   mysql数据库下
mysql中没有oracle的rownum，所以需要自己构建一个相似于oracle的rownum字段
select ifnull(
(select rownum
from
(
	select *,40 value
	from
	(select  a.*,(select count(*)from t where t.s>=a.s) as rownum from t a) fir     --构建类似的rownum字段
) sec
where value-s=
(
	select min(value-s) as cut
	from
		(
		select *,40 value
		from
		(select  a.*,(select count(*)from t where t.s>=a.s) as rownum from t a) fir   --构建类似的rownum字段
		) sec 
	where value-s>=0
) )
,
(select count(*)+1 as s from t)
) as rank
</code>