sql排名问题

[RainYang]

表tb
uid mark
1 9
2 5
3 8
4 6

想查出uid=4的名次：
uid mc
4 2
mysql版本4.0.20,是不是不能用子查询？哪位帮我看一下，多谢

[RainYang]

没人？

[RainYang]

写错了，mc应该是3
各位帮我顶顶也行啊

[mathematician]

uid=4的名次怎么得出3来的？根据mark排序吗？

[RainYang]

对，呵呵
uid mark
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的名次，不知道我说明白没有

[RainYang]

对，呵呵
uid mark
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的名次，不知道我说明白没有

[RainYang]

各位高手帮帮忙啊

[XqYuan]

成绩列表
select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum(mark) as total from tb group by uid) as a left join (select uid,sum(mark) as total from tb group by uid) as b on a.total<b.total group by uid order by grade

[XqYuan]

个人名次
select a.uid,a.total,if(b.uid is null,1,count(*)+1) as grade from (select uid,sum(mark) as total from tb where uid=4 group by uid) as a left join (select uid,sum(mark) as total from tb group by uid) as b on a.total<b.total group by uid

[RainYang]

楼上的，问题是我的mysql是4.0.20,不支持子查询吧

[XqYuan]

没有注意,mysql低版本的真麻烦

[XqYuan]

如果已经有总分,就不需要子查询了

[RainYang]

感谢大家！真的没什么好办法，另建了汇总表，，，麻烦啊

[mathematician]

不用子查询的确不好做，不行就用临时表吧！

[「已注销」]

XqYuan() 的方法不是很好，消耗太大了，如果有1000条记录就差不多相当于查50-100W的数据量，如果有10000条记录基本上就查不出来结果了吧。。。

[XqYuan]

那个查询只能看一下,肯定不好用

这样的查询好像不必要非要一次查出来

[「已注销」]

。。。
简单点，就是Top 10，不也一样是全部？

唯一只有指定uid的情况下才会好些。

[「已注销」]

“这样的查询好像不必要非要一次查出来”
恩，是的。

[「已注销」]

另外，在实际应用中，这样的问题是不能直接查原始数据表得到结果的，
应该存在统计结果的综合报表。

使用适当的冗余字段，表 是有好处地。：P

[yh801216_1]

呵呵，统计时可以用这样的语句：P

select uid, sum(mark) as total from tab_name group by uid order by total desc;

[yh801216_1]

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(mark) as total from tab_name group by uid order by total desc;