UC知道ranking的sql语句
来源：百度知道 编辑：UC知道 时间：2024/09/28 08:15:16
写了一个网站，php+mysql的
其中涉及到积分排名的问题
单独的查找排名，就直接用
select count(*) as ranking from user where user.score >(select score from user where username = '{$username})';
目前我想实现把
username，score，ranking全都列表显示
如
|username|score|ranking|
| aaa | 18 | 1 |
| bbb | 15 | 2 |
| ccc | 12 | 3 |
但是如何设计这个Query？？
总不能每个user取一次ranking
花了好长时间研究多表查询和子查询，一直没研究出来
to qsc800528：
我按照你给的sql把字段名改成E文的测试了下
select * , ranking =(select count(1) from
(SELECT name , SUM(score) as 'totalscore'FROM tb GROUP BY name) a
where totalscore> t.totalscore) + 1 from
(SELECT name , SUM(score) as 'totalscore'FROM tb GROUP BY name) t;
报错ERROR 1054 (42S22): Unknown column 'ranking' in 'field list'
而且我的username有unique属性
其中涉及到积分排名的问题
单独的查找排名，就直接用
select count(*) as ranking from user where user.score >(select score from user where username = '{$username})';
目前我想实现把
username，score，ranking全都列表显示
如
|username|score|ranking|
| aaa | 18 | 1 |
| bbb | 15 | 2 |
| ccc | 12 | 3 |
但是如何设计这个Query？？
总不能每个user取一次ranking
花了好长时间研究多表查询和子查询，一直没研究出来
to qsc800528：
我按照你给的sql把字段名改成E文的测试了下
select * , ranking =(select count(1) from
(SELECT name , SUM(score) as 'totalscore'FROM tb GROUP BY name) a
where totalscore> t.totalscore) + 1 from
(SELECT name , SUM(score) as 'totalscore'FROM tb GROUP BY name) t;
报错ERROR 1054 (42S22): Unknown column 'ranking' in 'field list'
而且我的username有unique属性
2）报错是说'ranking'这个字段在数据库里不存在，你在'ranking'后面＋个空格试试，或者你把'ranking'放到那个字段的后面，或者按照你写的as的方法给字段重新命名就可以了
1）楼主的排名不对，我按照你上面的做了一个例子，你参照改改字段名吧
if object_id('pubs..tb') is not null
drop table tb
go
create table tb
(
name varchar(10),
score int
)
insert into tb(name,score) values('1',80)
insert into tb(name,score) values('1',40)
insert into tb(name,score) values('1',80)
insert into tb(name,score) values('2',50)
insert into tb(name,score) values('2',40 )
insert into tb(name,score) values('2',60 )
select * , 排名 =(select count(1) from
(SELECT name , SUM(score) as '总分'FROM tb GROUP BY name) a
where 总分> t.总分) + 1 from
(SELECT name , SUM(score) as '总分'FROM tb GROUP BY name) t
drop table tb
select *,
(SELECT count(1) FROM tb GROUP BY name having totalscore>t.totalsco