草庐IT

MySQL 跨多列排名

coder 2023-10-22 原文

我们有一个包含以下数据的 MySQL 表(结果)

|nid|uid|score|duration

在这张表中，我们有来自多个测验的结果， 我们需要提出一个查询，为我们提供每个测验的用户排名输出。

排名必须基于最高分到最低分，如果分数与最低持续时间和最高持续时间持平。我们目前有这个查询:

  SELECT nid, uid, score, duration, rank FROM
    (SELECT nid, uid, score, duration,
    @curRank := IF(@prevRank = score + duration, @curRank, @incRank) AS rank,
    @incRank := @incRank + 1,
    @prevRank := score
    FROM results p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
    ) r WHERE nid = 1
    ORDER BY score DESC, duration ASC) s

然而，如果得分和持续时间并列，他们将获得不同的排名

输出

|nid|uid|score|duration|rank
|1  |1  |90   |100     |1
|1  |2  |90   |150     |2
|1  |3  |90   |150     |3
|1  |4  |80   |100     |4
|1  |5  |80   |200     |5
|1  |6  |70   |300     |6

期望的输出

|nid|uid|score|duration|rank
|1  |1  |90   |100     |1
|1  |2  |90   |150     |2
|1  |3  |90   |150     |2
|1  |4  |80   |100     |4
|1  |5  |80   |200     |5
|1  |6  |70   |300     |6

最佳答案

您需要为列使用单独的变量。此外，在子查询内进行排序，然后应用交叉连接并查找排名等。

试试这个:

SELECT 
    t.*,
    @rank:=IF((@rn:=@rn + 1) IS NOT NULL,
        IF(score = @lastscore
                AND duration = @lastduration,
            @rank,
            IF((@lastscore:=score) IS NOT NULL
                    AND (@lastduration:=duration) IS NOT NULL,
                @rn,
                1)),
        1) rank
FROM
    (SELECT 
        *
    FROM
        results
    WHERE nid = 1
    ORDER BY score DESC , duration , uid) t
        CROSS JOIN
    (SELECT @rank:=0, @lastscore:=- 1, @lastduration:=- 1, @rn:=0) t2

您可能会想在不同的列中分别分配值(就像您在问题中所做的那样)。 请注意，该方法可能不会像您预期的那样工作，因为 MySQL 可能不会按照与列出的列相同的顺序进行分配。

MySQL documentation对此很清楚:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

演示 @ SQLFiddle

关于MySQL 跨多列排名，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41766374/

多列排名durationscoresectionmysql

有关MySQL 跨多列排名的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. ruby-on-rails - Rails 单选按钮 - 模型中多列的一种选择 - 2

    我希望用户从一个模型的三个选项中选择一个。即我有一个模型视频，可以被评为正面/负面/未知目前我有三列bool值(pos/neg/unknown)。这是处理这种情况的最佳方式吗？为此，表单应该是什么样的？目前我有类似的东西但显然它允许多项选择，而我试图将它限制为只有一个..怎么办？ 最佳答案 如果要使用字符串列，让我们说rating。然后在你的表单中:#...#...它只允许一个选择编辑完全相同但使用radio_button_tag: 关于ruby-on-rails-Rails单选按钮-模

  3. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  4. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  5. ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2

    我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。

  6. ruby - 续集选择了太多列 - 2

    似乎Sequel的默认选择是“select*”，当您添加一些连接时会导致各种问题。至少你最终会在你的对象中得到错误的id(因为这样会返回不止一个“id”列)。做类似的事情.select("people.*")似乎可以工作，但它将传入的字符串视为一列并用引号引起来。到目前为止，我不得不恢复到裸SQL来解决这个问题，但我知道必须有更好的方法。 最佳答案 Sequel的默认行为是选择所有列，但很容易覆盖。如果您只想从单个表中选择所有列:.select(:people.*)如果您想使用文字SQL字符串:.select('people.*'.

  7. Centos7-yum安装mysql-修改密码-无密码登录-安全配置 - 2

    目录1、yum安装mysql修改密码(1)在mysql里面修改(2)第二种方式，利用mysqladmin修改密码2、没有密码，登录mysql修改密码3、mysql的安全设置1、yum安装mysql在CentOS中默认安装有MariaDB（MySQL的一个分支），安装完成之后可以直接覆盖MariaDB。rpm-qa|grepmariadb查询是否安装了mariadbrpm-e--nodepsmariadb-libs-5.5.60-1.el7_5.x86_64卸载mariadwgethttp://dev.mysql.com/get/mysql57-community-release-el7-11.

  8. ruby - 如何在 postgresql 中创建多列推荐引擎？ - 2

    我在postgresql中有一个表，其中包含一些汽车+1000000条记录:+----+--------+------+---------+-----------+-------------+------------+------------+|id|price|year|mileage|fuel_type|body_type|brand|model|+----+--------+------+---------+-----------+-------------+------------+------------+|1|4894|2011|121842|"Benzin"|"Sedan

  9. ruby - 安装 dm-mysql-adapter 时出错 - 2

    我是Ruby的新手。我安装了DataMapper并且正在尝试安装dm-mysql-adapter-1.0.2gem。但是当我尝试安装时，出现以下错误。我正在使用ubuntu操作系统。vinoth@vinoth-laptop:~/Downloads$geminstalldm-mysql-adapter-1.0.2----with-mysql-lib=/usr/lib/mysql----with-mysql-conf=/usr/bin/mysqlWARNING:Installingto~/.gemsince/home/vinoth/gemsand/home/vinoth/gems/bina

  10. ruby-on-rails - gem 列表中的 mysql2 gem 但获取项目找不到 gem - 2

    我目前正在构建一个需要mysql2gem的RoR项目。我成功安装了gem。因为它出现在我的gem列表中。[root@vc2cmmka035538nsimple_cms]#gemlist***LOCALGEMS***actionmailer(3.2.3)actionpack(3.2.3)activemodel(3.2.3)activerecord(3.2.3)activeresource(3.2.3)activesupport(3.2.14,3.2.3)arel(3.0.2)bigdecimal(1.1.0)builder(3.2.2,3.0.0)bundler(1.1.5)c2c_li

随机推荐