sql 排名 并列问题

dergao 2007-01-04 04:41:18


select ROWNUM=(select sum(1) from member where member_mark>=a.member_mark),
member_id,member_mark
from member a order by ROWNUM

正确的结果应该是 mopaco 是第2名才对


1,老李 ,100
3,mopaco ,90
3,taotao3166,90
6,跑车 ,70
6,ceagle ,70
6,小崔 ,70
...全文
316 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
yangys 2007-01-04
  • 打赏
  • 举报
回复
select ROWNUM=(select count(DISTINCT member_mark) from member where member_mark>=a.member_mark),
member_id,member_mark
from member a order by ROWNUM
yangys 2007-01-04
  • 打赏
  • 举报
回复
select ROWNUM=(select count(DISTINCT member_mark) from member where member_mark>=a.member_mark),
member_id,member_mark
from member a order by ROWNUM
yangys 2007-01-04
  • 打赏
  • 举报
回复
select ROWNUM=(select sum( DISTINCT member_mark) from member where member_mark>=a.member_mark),
member_id,member_mark
from member a order by ROWNUM
dergao 2007-01-04
  • 打赏
  • 举报
回复
这个是我自己的答案 是同学想的

select 名次=(select sum(1) from member where member_mark>a.member_mark or member_id=a.member_id),
member_id,member_mark
from member a order by 名次
dergao 2007-01-04
  • 打赏
  • 举报
回复
这个是我自己的答案 是同学想的
select 名次=(select sum(1) from member where member_mark>a.member_mark or member_id=a.member_id),
member_id,member_mark
from member a order by 名次
dergao 2007-01-04
  • 打赏
  • 举报
回复
这个是我自己的答案 是同学想的
select 名次=(select sum(1) from member where member_mark>a.member_mark or member_id=a.member_id),
member_id,member_mark
from member a order by 名次
yudi010 2007-01-04
  • 打赏
  • 举报
回复

其实还有其他的解决方案
学习了
冷箫轻笛 2007-01-04
  • 打赏
  • 举报
回复
老大正解!
joy_jiangyan 2007-01-04
  • 打赏
  • 举报
回复
select ROWNUM=(select sum(1)+1 from member where member_mark>=a.member_mark),
member_id,member_mark
from member a order by ROWNUM
子陌红尘 2007-01-04
  • 打赏
  • 举报
回复
select
ROWNUM=isnull((select count(*) from member where member_mark>a.member_mark),0)+1,
member_id,
member_mark
from
member a
order by
ROWNUM

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧