如何在MySql中减小此排名查询的大小？
如何在MySql中减小此排名查询的大小？
提问于 2010-08-05 04:21:15
我有一个排名查询，它对团队在挑战中的表现进行排名。
数据的层次结构如下:团队有成员，成员有活动，活动有活动类型，挑战有活动类型
如果我想对所有团队在一次挑战中的表现进行排名，这个查询非常有效：
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
ChallengeID，团队，排名99红队1 99蓝队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
但并不是每个挑战都有第一名和第二名，排名涵盖了所有的挑战。像这样
ChallengeID，团队，排名99红队1 99蓝队2 134红队3 134蓝队4 443红队5 442蓝队6
所以，我想，我在错误的地方评估排名，但我有点想不到如何做到这一点。如何获得这样的结果: ChallengeID，团队，排名99红色团队1 99蓝色团队2 134红色团队1 134蓝色团队2 443红色团队1 443蓝色团队2
回答 1
Stack Overflow用户
发布于 2010-08-05 15:40:53
听起来您正在寻找的是Oracle子句"PARTITION BY"，该子句将分组，然后允许您按照您正在尝试的方式进行多次排名。
可能最简单的方法是，您可以创建自己的排名，而不是尝试显示由mySQL生成的排名。
这是一个比我所能表达的更好的例子，嘿，快凌晨4点了！
http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3409501
复制相关文章
相似问题
腾讯云开发者
