排名sql

mysql> use test2;
Database changed
mysql> create table scores
    -> (
    -> user int not null auto_increment primary key
    -> ,
    -> scores int not null);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into scores(scores)
    -> values(80),(90),(7),(99),(100);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from scores;
+------+--------+
| user | scores |
+------+--------+
|    1 |     80 |
|    2 |     90 |
|    3 |      7 |
|    4 |     99 |
|    5 |    100 |
+------+--------+
5 rows in set (0.00 sec)


mysql> select user,scores,(select count(*) from scores where scores >=(select sc
ores from scores where user=4 order by scores desc limit 1)) as pm from scores w
here user=4;
+------+--------+------+
| user | scores | pm   |
+------+--------+------+
|    4 |     99 |    2 |
+------+--------+------+
1 row in set (0.00 sec)

 

 

function getTOP(&$db_class,$type='total',$user_id=NULL){
    $result = array();
    if($type=='total' && $user_id!==NULL){
        $sql ='SELECT user_id,total, ( SELECT count( * ) FROM top WHERE total >= ( SELECT total FROM top WHERE user_id ='.$user_id.' ORDER BY total DESC LIMIT 1 ) ) AS pm FROM top WHERE user_id ='.$user_id;
        $rs  = $db_class->query($sql);
        while($row = $db_class->fetchArray($rs)){
            if($row)  array_push($result,$row);
        }
   
    }else(($type=='type1' || $type=='type2' || $type=='type3') && $user_id!==NULL){
        $sql = 'SELECT user_id,'.$type.', (
            SELECT count( * )
            FROM top
            WHERE '.$type.' >= (
            SELECT '.$type.'
            FROM top
            WHERE user_id ='.$user_id.' ORDER BY '.$type.' DESC
            LIMIT 1 )
            ) AS pm
            FROM top
            WHERE user_id ='.$user_id;
        $rs  = $db_class->query($sql);
        $result = $db_class->fetchArray($rs)
    }
    return $result;
}




//total
replace into top (select p.user_id,count(v.pic_id),0,0,0 from tbl_vote v,tbl_pic p where v.pic_id=p.id group by v.pic_id)
//type1
replace into top (select p.user_id,t.total,count(v.pic_id),0,0 from tbl_vote v,tbl_pic p,top t where t.user_id=p.user_id and v.pic_id=p.id and v.type=1 group by v.pic_id)
//type2
replace into top (select p.user_id,t.total,t.type1,count(v.pic_id),0 from tbl_vote v,tbl_pic p,top t where t.user_id=p.user_id and v.pic_id=p.id and v.type=2 group by v.pic_id)
//type3
replace into top (select p.user_id,t.total,t.type1,t.type2,count(v.pic_id) from tbl_vote v,tbl_pic p,top t where t.user_id=p.user_id and v.pic_id=p.id and v.type=3 group by v.pic_id)


//total 排名
SELECT user_id, total, (
SELECT count( * )
FROM top
WHERE total >= (
SELECT total
FROM top
WHERE user_id =100011
ORDER BY total DESC
LIMIT 1 )
) AS pm
FROM top
WHERE user_id =100011;


//type1 排名
SELECT user_id, type1, (
SELECT count( * )
FROM top
WHERE type1 >= (
SELECT type1
FROM top
WHERE user_id =100011
ORDER BY type1 DESC
LIMIT 1 )
) AS pm
FROM top
WHERE user_id =100011;

//type2排名
SELECT user_id, type2, (
SELECT count( * )
FROM top
WHERE type2 >= (
SELECT type2
FROM top
WHERE user_id =100011
ORDER BY type2 DESC
LIMIT 1 )
) AS pm
FROM top
WHERE user_id =100011;

//type3排名
SELECT user_id, type3, (
SELECT count( * )
FROM top
WHERE type3 >= (
SELECT type3
FROM top
WHERE user_id =100011
ORDER BY type3 DESC
LIMIT 1 )
) AS pm
FROM top
WHERE user_id =100011;

 

-------------------------------------------------------------------------------------------------------------------

select (select count(*) from `top` where k.total<total or (k.total=total and k.user_id>=user_id)) as pm
from `top` k
where user_id=100011;