mysql with ties_sql ranking with custom ties

Hello can you help me get the rank with custom ties? i have a table of Scores stores all the scores given by the judges.

+----+----------+-------------+--------+

| Id | judge_id |performer_id | score |

+----+----------+-------------+--------+

| 1 | 1 | 1 | 98 |

| 2 | 1 | 2 | 98 |

| 3 | 1 | 3 | 94 |

| 4 | 1 | 4 | 96 |

| 5 | 2 | 1 | 93 |

| 6 | 2 | 2 | 80 |

+----+----------+-------------+--------+

heres what the code i have searched.

SELECT

id

, judge_id

, performer_id

, score

, FIND_IN_SET(

score

, (SELECT

GROUP_CONCAT(DISTINCT score ORDER BY score DESC)

FROM

scores

WHERE

judge_id = 1

)

) AS rank

FROM

scores

WHERE

judge_id = 1

ORDER BY rank ASC

and the output of this is:

+----+----------+-------------+--------+------+

| Id | judge_id |performer_id | score | rank |

+----+----------+-------------+--------+------+

| 1 | 1 | 1 | 98 | 1 |

| 2 | 1 | 2 | 98 | 1 |

| 3 | 1 | 4 | 96 | 3 |

| 4 | 1 | 3 | 94 | 4 |

+----+----------+-------------+--------+------+

it is working but the output is not what i want. i want to get the ranking and ties like this.

+----+----------+-------------+--------+------+

| Id | judge_id |performer_id | score | rank |

+----+----------+-------------+--------+------+

| 1 | 1 | 1 | 98 | 1.5 |

| 2 | 1 | 2 | 98 | 1.5 |

| 3 | 1 | 4 | 96 | 3 |

| 4 | 1 | 3 | 94 | 4 |

+----+----------+-------------+--------+------+

where get all the rank of the tie then divide it by how many performer ties in the rank.

ex.

performer 1 score 98 rank 1

performer 2 score 98 rank 1

suppose that performer 2 should get rank 2

i want to compute it like

1+2 = 3 then divide it by 2 since 2 performers are tie in rank 1

1=2 = 3 / 2

answer is 1.5

im sorry for my english

but please can any one help me? im stuck at this problem.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值