如何减少 MySql 中排名查询的大小？

Question

我有一个排名查询，可以对挑战中团队的表现进行排名。

数据的层次结构如下： 团队有成员 会员有活动 活动有活动类型 挑战有活动类型

如果我想对单个挑战中所有团队的表现进行排名，这个查询效果很好：

SELECT     t.teamID, t.teamName, 
        scoring.challengeID, 
        outerchallenge.name AS ChallengeName, outerchallenge.description AS ChallengeDescription, outerchallenge.startDate, outerchallenge.endDate, 
        scoring.standardValueSum, scoring.standardUnit, scoring.rank 
FROM challenge outerchallenge 
    LEFT JOIN ( 
        SELECT teamID, challengeID, standardValueSum, standardUnit, FIND_IN_SET(standardValueSum, scores ) AS rank 
        FROM ( 
            SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum, v.standardUnit 
            FROM v_activitystats v 
                INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
                INNER JOIN teammember ON v.memberID = teammember.memberID 
                INNER JOIN challenge c ON mc.challengeID = c.challengeID 
                INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
            WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
                AND c.challengeID = 33  
            GROUP BY standardUnit, challengeID, teamID 
            ) vstats 
    CROSS JOIN ( 
        SELECT GROUP_CONCAT( DISTINCT standardValueSum ORDER BY standardValueSum DESC ) AS scores 
        FROM ( 
            SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum 
            FROM v_activitystats v 
                INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
                INNER JOIN teammember ON v.memberID = teammember.memberID 
                INNER JOIN challenge c ON mc.challengeID = c.challengeID 
                INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
            WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
                AND c.challengeID = 33  
            GROUP BY challengeID, teamID 
            ) vstats 
        ) scores 
    ) scoring 

    ON outerchallenge.challengeID = scoring.challengeID 
        INNER JOIN team t ON scoring.teamID = t.teamID 

这是一个格式化的查询： http://mysql.pastebin.com/XggRL5kX

挑战ID、团队、排名 99 红队 1 99 Blue Team 2

再次，这对于特定的挑战来说效果很好，(ID = 33)

我想要获得具有相同排名的查询，但对于多个挑战，例如那些已经结束的挑战。

我尝试了这个查询：

SELECT rankings.teamID, stuff.teamName, rankings.challengeID, 
        rankings.ChallengeName, rankings.ChallengeDescription, rankings.startDate, rankings.endDate, 
        rankings.standardValueSum, rankings.standardUnit, rankings.rank 
FROM challenge chal 
    LEFT JOIN ( 
    SELECT t.teamID, t.teamName, scoring.challengeID, 
        outerchallenge.name AS ChallengeName, outerchallenge.description AS ChallengeDescription, outerchallenge.startDate, outerchallenge.endDate, 
        scoring.standardValueSum, scoring.standardUnit, scoring.rank 
    FROM challenge outerchallenge 
        LEFT JOIN ( 
            SELECT teamID, challengeID, standardValueSum, standardUnit, FIND_IN_SET(standardValueSum, scores ) AS rank 
            FROM ( 
                SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum, v.standardUnit 
                FROM v_activitystats v 
                    INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
                    INNER JOIN teammember ON v.memberID = teammember.memberID 
                    INNER JOIN challenge c ON mc.challengeID = c.challengeID 
                    INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
                WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
                GROUP BY standardUnit, challengeID, teamID ) vstats 
            CROSS JOIN ( 
                SELECT GROUP_CONCAT( DISTINCT standardValueSum ORDER BY standardValueSum DESC ) AS scores 
                FROM ( 
                    SELECT teammember.teamID, mc.challengeID, sum(v.standardValue) standardValueSum 
                    FROM v_activitystats v 
                        INNER JOIN memberchallenge mc ON v.memberID = mc.memberID AND v.standardValue > 0 
                        INNER JOIN teammember ON v.memberID = teammember.memberID 
                        INNER JOIN challenge c ON mc.challengeID = c.challengeID 
                        INNER JOIN challengeactivitytype cat ON c.challengeID = cat.challengeID AND cat.activityTypeID = v.activityTypeID 
                    WHERE v.activityDate BETWEEN c.startDate AND c.endDate 
                    GROUP BY challengeID, teamID 
                ) vstats 
            ) scores 
        ) scoring ON outerchallenge.challengeID = scoring.challengeID 
        INNER JOIN team t ON scoring.teamID = t.teamID 
) rankings ON chal.challengeID = rankings.challengeID
WHERE chal.endDate <= current_date()

这是一个格式化的查询： http://mysql.pastebin.com/mSZwtDm3

但并不是每个挑战都有第一名和第二名，而是所有挑战都有排名。喜欢这个

挑战ID、团队、排名 99 红队 1 99蓝队2 134 红队3 134 蓝队4 第443章 红队5 442 Blue Team 6

所以，我想，我在错误的地方评估了排名，但我对如何进行这项工作有点不知道。我怎样才能得到这样的结果： 挑战ID、团队、排名 99 红队 1 99蓝队2 134 红队 1 134 蓝队2 第443章 红队1 第443章 蓝队2

Answer 1

听起来您正在寻找的是 Oracle 子句“PARTITION BY”，它将进行分组，然后允许您像您尝试做的那样多次排名。

也许最简单的方法是您可以创建自己的排名，而不是尝试显示由 mySQL 生成的排名。

这是一个比我能阐述的更好的例子，嘿，现在已经快凌晨 4 点了！
http://www.xaprb .com/blog/2006/12/02/how-to-number-rows-in-mysql/