MySql 是否根据先前的值递增

Question

在下面的 sql 中，我试图根据 perCent 列增加排名。这里的问题是，当找到相似的分数时，排名应该保持不变。

select id, perCent, 
       @curRank := if( parent = @prevParent and perCent != @prevPerCent , @curRank + 1, 1) AS rank,

       @prevParent := parent, @prevPerCent := perCent
from (
    select child.id, child.perCent, child.parent
    from likesd parent
    join likesd child
       on parent.id = child.parent
    where parent.type = 3
    order by parent.id, child.perCent desc) x
cross join (SELECT @curRank := 0, @prevParent := null, @prevPerCent := null) r

例如:50 是第 1 级，30 是第 2 级，30 又是第 2 级，20 是第 3 级。想法是当找到相似的排名时不递增。

我该怎么做？我如何在 if 语句中告诉 MySql 不要增加它？

基本逻辑是

if parent = @prevParent, if perCent = @prevPerCent
// Dont increment
else
increment

我在 MySql 中遇到了这部分问题。你能帮忙吗？

主表

"id"    "type"  "parent"    "country"   "votes" "perCent"
"24"    "1"     "1"         "US"        "35"    "0"
"25"    "3"     "24"        "US"        "35"    "0"
"26"    "10"    "25"        "US"        "15"    "50.00"
"27"    "10"    "25"        "US"        "10"    "33.33"
"28"    "10"    "25"        "US"        "10"    "33.33"

"29"    "1"     "1"         "US"        "50"    "0"
"30"    "3"     "29"        "US"        "50"    "0"
"31"    "10"    "30"        "US"        "20"    "40.00"
"32"    "10"    "30"        "US"        "15"    "25.00"
"33"    "10"    "30"        "US"        "15"    "35.00"

预期结果:

"id"    "perCent" "rank"
"26"    "50.00"   "1"
"27"    "33.33"   "2" 
"28"    "33.33"   "2" // No increment in rank if the perCent is same as above

"31"    "40.00"    "1" // Continious incrementing here. PerCents differ.
"33"    "35.00"    "2"
"32"    "25.00"    "3"

再努力一点也没用(唉!我放弃了)

select id, perCent, 
      @curRank := if(parent = @prevParent, TRUE, FALSE) AS rank,

      @curCent := if(perCent = @prevCent, FALSE, TRUE) AS cent,

      @curRank := if(@curRank and @curCent, @curRank + 1,'No Inc') AS k,

       @prevParent := parent, @prevCent := perCent
from (
    select child.id, child.perCent, child.parent
    from likesd parent
    join likesd child
       on parent.id = child.parent
    where parent.type = 3
    order by parent.id, child.perCent desc) x
cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) r

Answer 1

试试这个:http://sqlfiddle.com/#!2/ac996/7

select id, perCent, rank
from
(
    select id, perCent 
    , @curRank :=   
        case 
           when @prevParent = parent then
               case
                   --if parents are the same and values are the same, rank is the same
                   when @prevCent = perCent then @curRank 
                   --if same parents but different values, increment the rank
                   else @curRank + 1
               end
           --if parents are different, reset the rank
           else 1
        end rank
    , @prevParent := parent
    , @prevCent := perCent
    from 
    (
        select child.id, child.perCent, child.parent
        from likesd parent
        inner join likesd child
           on child.parent = parent.id
        where parent.type = 3
        order by parent.id
        , child.perCent desc
    ) x
    cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) r
) y