成绩列表 select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum() as total from tb group by uid) as a left join (select uid,sum() as total from tb group by uid) as b on a.total<b.total group by uid order by grade
个人名次 select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum() as total from tb where uid=4 group by uid) as a left join (select uid,sum() as total from tb group by uid) as b on a.total<b.total group by uid
呵呵,统计时可以用这样的语句:Pselect uid, sum() as total from tab_name group by uid order by total desc;
create table tab_stat ( id int(10) unsigned not null auto_increment, uid smallint(5) unsigned not null default 0, total smallint(5) unsigned not null default 0, primary key id, key uid index_uid (uid) )type=myisam;insert into tab_stat (uid, total) select uid, sum() as total from tab_name group by uid order by total desc;
uid
1 7
1 6
2 3
2 2
2 5
3 4
3 3
4 8
4 1
4 3
uid的总分是8+1+3,求uid的名次,不知道我说明白没有
uid
1 7
1 6
2 3
2 2
2 5
3 4
3 3
4 8
4 1
4 3
uid的总分是8+1+3,求uid的名次,不知道我说明白没有
select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum() as total from tb group by uid) as a left join (select uid,sum() as total from tb group by uid) as b on a.total<b.total group by uid order by grade
select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum() as total from tb where uid=4 group by uid) as a left join (select uid,sum() as total from tb group by uid) as b on a.total<b.total group by uid
简单点,就是Top 10,不也一样是全部?唯一只有指定uid的情况下才会好些。
id int(10) unsigned not null auto_increment,
uid smallint(5) unsigned not null default 0,
total smallint(5) unsigned not null default 0,
primary key id,
key uid index_uid (uid)
)type=myisam;insert into tab_stat (uid, total) select uid, sum() as total from tab_name group by uid order by total desc;