将递增值合并到此sql中 [英] Incorporating an incrementing value into this sql

问题描述

我正在尝试执行与此问题中的第一个答案类似的事情 c0>输出排名，以某种方式我无法正确显示.

I'm trying to do something like the first answer in this question Adding the @part which outputs a rank, somehow I'm not able to get it right.

我正在使用的sql是:

The sql I'm using is:

select child.id, child.perCent
from likesd parent
join likesd child
   on parent.id = child.parent
where parent.type = 3
order by parent.id, child.perCent desc;

我有些无法适应上述sql中的@部分，并且在那里需要帮助.

I some how cannot fit the @ parts inside the above sql, and need help there.

SELECT    first_name, // This sql is from the previous question
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

所以:

select child.id, child.perCent, @curRank := @curRank + AS rank
    from likesd parent, (SELECT @curRank := 0) r
    join likesd child
       on parent.id = child.parent
    where parent.type = 3
    order by parent.id, child.perCent desc;

最后，我要实现的目标是期望的结果.您能看到我该怎么做吗?

In the end, what I'm trying to achieve is in the Desired results. Can you see how I can do this?

主表

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

"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" // Rank is calculated based on the sql order above
"26"    "50.00"   "1" 
"27"    "33.33"   "2"
"28"    "16.66"   "3"

"31"    "40.00"    "1" // New parent, new ranking
"33"    "35.00"    "2"
"32"    "25.00"    "3"

推荐答案

select id, perCent, 
       @curRank := if(parent = @prevParent, @curRank + 1, 1) AS rank,
       @prevParent := parent
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) r

FIDDLE

这篇关于将递增值合并到此sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！