mysql - 为 "Order By"整数数据创建排名 - MySQL

Question

我正在开展一个项目，其中有一些出勤数据。我希望能够打印排名前 # 的与会者。

我的查询设置为按每个人参加的事件数量对列表进行排序。我允许用户设置限制(例如，前 50 名与会者)。问题是，这对解释关系没有任何作用，所以我想在查询中生成一个排名，然后可以用它来限制。

我的相关架构如下:

成员表:

Member Name | Member ID | # Events Attended

事件表:

Event Name | Event ID | Other Stuff

然后，该表用作出勤表的外键，该表通过使用组合了成员和事件 ID 的外键将成员链接到事件。

考勤表:

Attendance Log ID | Member FK | Event FK

所以，我的查询是这样的:

SELECT  `Member Name`, `Member ID` , COUNT(  `Member ID` ) AS Attendances
FROM  `Members` m
INNER JOIN 
(SELECT *
 FROM  `Events` e
 INNER JOIN  `Attendance` r ON  `Event ID` =  `Event FK`
) er
ON  `Member ID` =  `Member FK`
GROUP BY  `Member ID`
ORDER BY `Attendances` DESC

总而言之，如何创建一个可用于限制结果的“排名”？因此，前 50 名与会者是排名前 50 名的与会者(因此 #entries >= 50)，而不是 50 个人(# 条目始终为 50，切断联系)。

谢谢大家!

编辑1:

无限制查询的示例输出(显示所有结果):

Member Name | Member ID | Attendances
Bob Saget               1                    5
John Doe                2                    4
Jane Doe                3                    3
Stack Overflow          4                    3

因此，当用户使用我当前的查询请求“显示前 3 位与会者”时，他们会得到以下内容:

Member Name | Member ID | Attendances
Bob Saget               1                    5
John Doe                2                    4
Jane Doe                3                    3

实际上，我希望它显示领带并显示类似的内容

Rank | Member Name | Member ID | Attendances
1             Bob Saget              1               5
2             John Doe               2               4
3             Jane Doe               3               3
3             Stack Overflow         4               3

Answer 1

你可以试试这个:-

SELECT IF(Attendances = @_last_Attendances, @curRank:=@curRank, @curRank:=@_sequence) AS rank, 
       @_sequence:=@_sequence+1,@_last_age:=age, Member Name, Member ID, 
       COUNT(  `Member ID` ) AS Attendances     
FROM `Members` m
INNER JOIN (SELECT * FROM  `Events` e
            INNER JOIN  `Attendance` r 
            ON  `Event ID` =  `Event FK`) er
ON  `Member ID` =  `Member FK`, 
(SELECT @curRank := 1, @_sequence:=1, @_last_Attendances:=0) r
GROUP BY  `Member Name`, `Member ID`, Rank
HAVING COUNT( `Member ID`) >= (SELECT MAX (`Member ID`) 
                               FROM `Members`
                               WHERE `Member ID` < (SELECT MAX (`Member ID`)  
                                                    FROM `Members`
                                                    WHERE `Member ID` < (SELECT MAX (`Member ID`)  
                                                                         FROM `Members`)))
    ORDER BY COUNT(`Member ID`) DESC;

我认为这种方法会对您有所帮助。