我提出一个排名问题,把工作队的工作排在挑战中。

The hierarchy of data is as follows: teams have members members have activities activities have activitytypes challenges have activitytypes

如果我想把所有团队的工作排在同一个挑战中的话,那么这一难题就非常令人信服:

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 

Here is a formatted query: http://mysql.pastebin.com/XggRL5kX

ChallengeID, Team, Ranking 99 Red Team 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()

Here is a formatted query: http://mysql.pastebin.com/mSZwtDm3

但是,并非每一个挑战都存在第1位,第2位,而是所有挑战的排名。 和

ChallengeID, Team, Ranking 99 Red Team 1 99 Blue Team 2 134 Red Team 3 134 Blue Team 4 443 Red Team 5 442 Blue Team 6

So, I suppose, I m evaluating the ranking at the wrong place, but I m sort of out of ideas for how to make this work. How can I get results like this: ChallengeID, Team, Ranking 99 Red Team 1 99 Blue Team 2 134 Red Team 1 134 Blue Team 2 443 Red Team 1 443 Blue Team 2