求sql语句

[davytao1018]

有记录：
uname,uemail,score
-----------------------
a [e-mail] 2
b [e-mail] 2
c [e-mail] 1
d [e-mail] 1
e [e-mail] 0

要求查询结果：
uname,uemail,score,sequence
-----------------------------
a [e-mail] 2 1
b [e-mail] 2 1
c [e-mail] 1 3
d [e-mail] 1 3
e [e-mail] 0 5

sequence的值根据score排序而得

[WangZWang]

Select *, sequence=(Select count(*) from 表 where score>=t.score)
from 表 as t

[wangdehao]

Select *, sequence=(Select count(*) from 表 where score>t.score)+1
from 表 as t

[hrb2008]

select uname,uemail,score,mc=(select count(1) from table where score>=a.score) from table a

[hrb2008]

select uname,uemail,score,mc=(select count(1) from table where uname=a.uname and score>=a.score) from table a

[marco08]

create table T(uname char(1), uemail varchar(20), score int)
insert T select 'a', '[e-mail]', 2
union all select 'b', '[e-mail]', 2
union all select 'c', '[e-mail]', 1
union all select 'd', '[e-mail]', 1
union all select 'e', '[e-mail]', 0

select *,
[sequence]=(select col=count(*) from T where uname<=tmp.uname)
into #T
from T as tmp
order by score desc

select uname, uemail, score,
[sequence]=(select min([sequence]) from #T where score=tmp.score)
from #T as tmp

--result
uname uemail score sequence
----- -------------------- ----------- -----------
a [e-mail] 2 1
b [e-mail] 2 1
c [e-mail] 1 3
d [e-mail] 1 3
e [e-mail] 0 5

(5 row(s) affected)

[w75251455]

create table tt(uname char(10),uemail char(20),score int)
insert tt select 'a', '[e-mail]', 2
union all select 'b', '[e-mail]', 2
union all select 'c', '[e-mail]', 1
union all select 'd', '[e-mail]', 1
union all select 'e', '[e-mail]', 0

select *,
(select count(1)-(select count(1)-1 from tt c where c.score=a.score)from tt b where b.score>=a.score)nce
from tt a


/*
uname uemail score nce
a [e-mail] 2 1
b [e-mail] 2 1
c [e-mail] 1 3
d [e-mail] 1 3
e [e-mail] 0 5
*/

[WangZWang]

--正确的方法
Select *, sequence=(Select count(*) from 表 where score>t.score)+1
from 表 as t

--SQL2005
Select *, Rank() over (order by score desc) as sequence
from 表 as t

[w75251455]

嗯~~
为做题而来~~~方法很多~~不能重复!