MySQL增量或不基于以前的值

debugcn 发表于 Dev

名字

在下面的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

约翰·贝文

试试这个：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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。