主页 / dba / 问题 / 89541
Accepted
Kim Stacks
Asked: 2015-01-18 07:32:26 +0800 CST

如何在 mysql 中自动分配序数排名，并尽可能少地查询新行？

  • 772
+----+---------+---------+-----------------+
| id | view_id | user_id | ordinal_ranking |
+----+---------+---------+-----------------+
|  1 |      10 |       1 |               1 |
|  2 |      10 |       2 |               1 |
|  3 |      11 |       1 |               2 |
|  4 |      11 |       2 |               2 |
+----+---------+---------+-----------------+

所以假设我想为 view_id 12 和 user_id 1 和 2 插入新行，这 2 个新行的序数应该是 3。

如何用最少的查询来做到这一点？

请记住，我有很多用户可能都分配了不同数量的视图。

这个问题的另一个推论是当我需要删除一个特定的视图时，比如视图 10，所有受影响的用户都应该重新排序。

我如何用最少的查询来做到这一点？

编辑

对每个用户的观看次数发表了评论。我想补充一点，您可以放心地假设有一个users表，其中一列存储每个用户记录的视图数。

如果序数排名是从零开始的，我也可以，即排名是0、1、2、3等

我也不喜欢使用存储过程。

编辑2：

+----+---------+---------+
| id | name | view_count |
+----+---------+---------+
|  1 |     Sal |       2 |
|  2 |     Sam |       2 |
+----+---------+---------+

是users表

这ordinal_ranking仅反映了用户对她可以查看的视图的个人选择。

例如，用户 1 Sal 喜欢让视图 10 的排名高于视图 11。

mysql performance

2 个回答

  1. 如果您对每个 user_id 添加和删除 view_id 感到满意，这就是我所看到的

    将视图 12 添加到用户 10

    我看到三个查询

    • 使用 BogusRank 将新视图插入用户
    • 将所有排名移至 NewRank 以下
    • 将 BogusRank 更改为 New Rank 以获得新视图

    这是建议的代码

    SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking >= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;

    从用户 10 中删除视图 12

    我看到三个查询

    • 检索带有要删除的视图的行
    • 在排名中上移所有视图
    • 使用旧视图删除行

    这是建议的代码

    SET @view_id = 12;
SET @user_id = 10;
SELECT id,ordinal_ranking INTO @id_to_zap,@rank_to_zap FROM userviewranks
WHERE view_id = @view_id AND user_id = @user_id;
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking - 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking > @rank_to_zap;
DELETE FROM userviewranks WHERE id = @id_to_zap;
    • 1
  2. Best Answer

    假设调用了表users_access_views并且主键id是自动递增的并且假设有一个users表，

    场景 1a) 插入新视图并使其成为所有至少有权访问 1 个视图的用户的最后一个视图

    无论每个用户已经分配了多少视图，并且这个新的 view_id 为 3，都插入一个新的 view_id 并确保遵守序数排名，

    INSERT INTO users_access_views (view_id, user_id, ordinal_ranking) 
SELECT 3, user_id, count(id) + 1 from users_access_views group by user_id;

    场景 1b) 插入新视图并让它成为所有选定用户的最后一个视图，无论他们是否已经有任何视图

    另一方面，如果您想添加选定的用户，并且其中一些用户可能还没有访问任何视图的权限，让我们假设所涉及的用户是用户 1、2、3

    INSERT INTO users_access_views (view_id, user_id, ordinal_ranking) 
SELECT 3, u.id, count(uav.id) + 1 from users_access_views uav
  RIGHT JOIN users u ON uav.user_id = u.id 
WHERE u.id in (1, 2, 3) 
GROUP BY u.id;

    场景2）删除现有视图并在删除后重新排序ordinal_ranking

    要删除现有的 view_id，无论每个用户已经分配了多少，并且这个删除的 view_id 是 2，并确保遵守序数排名，

    -- delete all the view_id 2 first

DELETE FROM users_access_views where view_id = 2;

-- update the ordinal ranking 

UPDATE users_access_views ua4 INNER JOIN

-- this subquery temp table inspired by http://stackoverflow.com/a/25763224/80353    
(SELECT ua1.id, ua1.user_id, ua1.view_id, count(ua2.id) + 1 as rank
FROM  users_access_views ua1 LEFT JOIN users_access_views ua2 ON 
ua1.ordinal_ranking>ua2.ordinal_ranking and ua1.user_id = ua2.user_id 
GROUP BY  ua1.id, ua1.user_id, ua1.view_id) ua3 

ON ua3.id = ua4.id 
SET
  ua4.ordinal_ranking = ua3.rank;

    如果需要，请在每个场景之后使用另一个查询来更新view_count表中的users。

    • 1

相关问题