MySQL查询可从一张表中获得最佳排名，并通过另一张表进行更新 [英] MySQL query to get best Ranks from one table and UPDATE with results another

问题描述

我有一个排名委员会，该排名委员会针对男性和女性的头100个最佳博客.

I have a Ranking board for the best first 100 blogs for each gender Males and Females.

我有一个博客表:

PRIMARY
blogs_id  users_id  blogs_score  blogs_score_time      gender
    1         11        2852     2015-09-09 05:21:51     m <-- same score but older date
    2         23        2146     2015-09-10 07:31:54     m
    3         23        2146     2015-09-10 07:32:26     m
    4         23        2852     2015-09-10 04:42:15     m <-- same score but newer date
    5         51        1793     2015-09-11 08:15:55     f
    6         88        2947     2015-09-11 09:33:18     f 

我有一个用户表:

PRIMARY
  id  best_rank  gender
  11      0         m
  23      0         m
  51      0         f
  88      0         f

我需要计算博客表中每个用户的最佳排名(使用MAX(blogs_score)和MAX(blogs_score_time))，并使用/插入/更新 Users表性别最高的前100个用户的最佳排名，因此结果应为:

I need to COUNT best ranks (using MAX(blogs_score) and MAX(blogs_score_time)) for each user from Blogs table and INSERT/UPDATE the Users table with the best ranks for the first 100 users of each gender with highest ranks, so the result should be:

PRIMARY
  id  best_rank  gender
  11      2         m
  23      1         m
  51      2         f
  88      1         f

其中用户ID 11的最佳等级为2，因为用户ID 11与用户23的得分相同，但日期早于用户23.其中，用户51由于女性的组而具有等级2.我添加了列blogs_score_time以防止平局排名.日期2015-09-10表示9月10日.

Where user id 11 has best rank of 2 because user id 11 has same score with user 23 but older date than user 23. Where user 51 has rank 2 because of Female's group. I have added column blogs_score_time to prevent tie ranking. And the date 2015-09-10 means September 10.

最终的想法是显示用户个人资料中的排名位置，一旦有人投票(博客表中的blogs_score更改)，则必须使用新的内容更新users表中的best_rank列对 ALL 100个在特定性别组中排名最高的用户进行排名重新计算.

The final idea is to show the Rank position in user's profile and once someone voted (blogs_score changes in the blogs table), the best_rank column in the users table must be updated with the new ranks recalculation for ALL 100 users who has the best ranks in particular gender group.

如何根据我的需要调整并修复此(无效)查询?

How to adapt and fix this (not working) query to my needs?

 $sql->query("UPDATE users
            JOIN (SELECT b.users_id,
                         @curRank := @curRank + 1 AS rank,
                         MAX(blogs_score), MAX(blogs_score_time)
                  FROM blogs b
                         JOIN (SELECT @curRank := 0) r
                  ORDER BY  b.blogs_score DESC, p.blogs_score_time DESC LIMIT 100
                 ) ranks ON (ranks.users_id = users.id)
            SET users.best_rank = ranks.rank");

或者如何适应和修复此(无效)查询

Or how to adapt and fix this (not working) query

 $sql->query("INSERT INTO users (best_rank, id)
                  SELECT @rank := @rank + 1 AS rank, b.users_id
                       FROM blogs b
                       LEFT JOIN users u ON b.users_id = u.id
                  GROUP BY b.users_id, b.blogs_score, b.blogs_score_time, b.gender
                  ORDER BY b.blogs_score DESC, b.blogs_score_time DESC
              ON DUPLICATE KEY UPDATE best_rank = VALUES(rank)");

请帮助我解决该查询.我没有在stackoverflow上找到任何类似的解决方案，对我来说，单独编写这样的查询似乎非常复杂.

Please help me with the query to solve it. I didn't find any similar solution on stackoverflow and it seems very complicated for me to write such a query alone.

非常感谢！

推荐答案

UPDATE users u, (
SELECT 
( 
    CASE gender 
    WHEN @curType 
    THEN @curRow := @curRow + 1 
    ELSE @curRow := 1 AND @curType := gender END
) + 1 AS rank, users_id, blogs_score, blogs_score_time, gender 
FROM blogs, 
(SELECT @curRow := 0, @curType := '') r
ORDER BY gender, blogs_score DESC, blogs_score_time DESC
) r1
SET u.best_rank = r1.rank
WHERE
u.id = r1.users_id

它有2个工作原理:

1. 内部选择查询根据性别，分数和时间生成排名
2. 更新具有相应等级的用户表.

这篇关于MySQL查询可从一张表中获得最佳排名，并通过另一张表进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！