如何在MySql中对分区进行排名 [英] how to rank over partition in MySql

问题描述

我是新使用 MySql 数据库，我面临的问题是如果在 SQL 服务器数据库中我可以解决它，但我不能在 mysql 中这样做，这在我的情况下

Im new use MySql database, I face the problem that I can solve it if in SQL server Database, but I cant do it in mysql this bellow my case

我的表:

Name    Price
abs     100
abs     200
abs     60
trx     19
trx     20
abs     10
qwe     25
qwe     50
qwe     10
qwe     10

预期结果:

Name    Price   Rank
abs      200    4
abs      100    3
abs      60     2
abs      10     1
qwe      50     4
qwe      25     3
qwe      10     2
qwe      10     1
trx      20     2
trx      19     1

谁能帮助我如何进行像索引结果图片这样的查询用mysql

could anyone help me how to make query like index result pict with Mysql

推荐答案

使用变量可以找到Rank.像这样:

Using variable you can find Rank. Like this:

SELECT Name, Price, Rank
FROM (
      SELECT Name,
             Price,
             CASE WHEN @name = Name
                     THEN @id:=@id+1
                  ELSE @id:=1
             END AS Rank,
             @name:= Name AS dummy
      FROM myTable, (SELECT @name:=NULL, @id:=0) AS t
      ORDER BY Name,Price
     ) AS x
ORDER BY Name, Price DESC, Rank DESC 

输出:

Name    Price   Rank
---------------------
abs      200    4
abs      100    3
abs      60     2
abs      10     1
qwe      50     4
qwe      25     3
qwe      10     2
qwe      10     1
trx      20     2
trx      19     1

演示链接:

http://sqlfiddle.com/#!9/a98575/4

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