在mysql中排名 [英] rank over in mysql

问题描述

我阅读了

I read this article, and i have the code in oracle, but I want to convert it to work on MySQL. In Oracle, i use the function rank, with four columns can eligible or not, how i can use this in mysql, or, it is not possible?

这是代码，我想选择最合适的行，每行可以完成4列，我想对其中一个包含更多数据的行进行排名.

This is the code, I want select the most eligible line, every line can have 4 columns completed, I want to rank one of which has more data.

SELECT vlr,
       data
INTO   vn_vlr,
       vd_data
FROM (SELECT a.*, rank() over (ORDER BY nvl(a.id_categoria, -1) DESC,
             nvl(a.id_peso, -1) DESC,
             nvl(a.id_faixa, -1) DESC,
             nvl(a.sexo, ' ') DESC ) rank
        FROM tab_regra_pagamento a
       WHERE a.id_competicao = pidcompedticao
         AND a.tipo = 'NORMAL'
         AND a.data_vencimento > SYSDATE
         AND nvl(a.id_categoria, vid_categoria) = vid_categoria
         AND nvl(a.id_faixa, vid_faixa) = vid_faixa
         AND nvl(a.id_peso, vid_peso) = vid_peso
         AND nvl(a.sexo, vsexo) = vsexo)
WHERE  rank = 1;

推荐答案

SELECT  @rank := @rank + (@value <> value),
        @value := value
FROM    (
        SELECT  @rank := 0,
                @value := -1
        ) vars,
        mytable
ORDER BY
        value

在这种情况下，当您只需要第一组值的所有副本时:

In you case, when you just need all copies of the first set of values:

SELECT  vlr, data
FROM    tab_regra_pagamento a
WHERE   a.id_competicao = pidcompedticao
        AND a.tipo = 'NORMAL'
        AND a.data_vencimento > SYSDATE
        AND (a.id_cetegoria, a.id_faixa, a.id_peso, a.sexo) =
        (
        SELECT  ai.id_cetegoria, ai.id_faixa, ai.id_peso, ai.sexo
        FROM    tab_regra_pagamento ai
        WHERE   ai.id_competicao = pidcompedticao
                AND ai.tipo = 'NORMAL'
                AND ai.data_vencimento > SYSDATE
        ORDER BY
                a.id_cetegoria DESC, a.id_faixa DESC, a.id_peso DESC, a.sexo DESC
        LIMIT 1
        )

这篇关于在mysql中排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！