时间戳

首页 > 解决方案 > 徽章系统 / MySQL GROUP BY 和 ORDER BY

mysql - 徽章系统 / MySQL GROUP BY 和 ORDER BY

问题描述

我正在为我的网站创建一个徽章系统：下面的屏幕截图显示了它现在的样子。在获得 10,100 等点以执行特定操作（例如回答问题、更正 wiki）后，您将获得具有多个级别（青铜、白银……）的徽章。请注意，带有美元符号的镜子有两次，一次是银色的，一次是青铜的：

徽章

底层是两张表，一张带有徽章信息（x_badges：存储什么操作以及其中的多少点为您提供了什么样的徽章级别），一张带有哪个用户获得了哪个徽章（x_badges_earned：badgeID 和 uID）。

FK的解释：

这是我最初的查询，每个类别只给我一个徽章（但不是最高的，即如果我在一个类别中获得银牌和铜牌，它将只返回铜牌）：

SELECT * 
FROM x_badges_earned, x_badges 
WHERE xbe_bID = xb_ID 
  AND xbe_uID = ? 
GROUP BY xb_requirement  
ORDER BY xb_requirement ASC, xb_requirement_value ASC

在对 SO 进行研究之后，我创建了这个查询来生成上面的屏幕截图。如果我将最后一个 GROUP BY 更改为 xb_requirement，我将返回查询 1 的结果：

SELECT b.* 
FROM x_badges b 
LEFT JOIN x_badges b2 ON b.xb_ID = b2.xb_ID 
                     AND b.xb_requirement_value < b2.xb_requirement_value 
LEFT JOIN x_badges_earned be ON be.xbe_bID = b.xb_ID 
WHERE b2.xb_requirement_value IS NULL 
  AND be.xbe_uID = ? 
GROUP BY b.xb_ID

我觉得我现在几乎在绕圈子。我想出了这个可能会起作用的方法（至少在我的脑海中），但我无法解决它：

SELECT *
FROM x_badges
WHERE xb_ID IN (
    SELECT xb_ID, MAX(xb_requirement_value)
    FROM x_badges_earned, x_badges
    WHERE xbe_bID = xb_ID AND xbe_uID = ?
    GROUP BY xb_requirement
)

任何帮助是极大的赞赏！

我附上了 MySQL 代码来创建这两个表并填充它们以供使用。此外，您可以使用 1 作为参数值，因为它是我的用户 ID。我当前的输出看起来像这样，如果没有第一行 (xb_ID 11)，所需的输出将是相同的，因为它是我获得银牌的青铜徽章。


CREATE TABLE `x_badges` (
  `xb_ID` int(11) NOT NULL,
  `xb_requirement` varchar(40) NOT NULL,
  `xb_requirement_value` int(11) NOT NULL,
  `xb_text` text NOT NULL,
  `xb_text_details` text NOT NULL,
  `xb_icon` varchar(20) NOT NULL,
  `xb_color` varchar(6) NOT NULL,
  `xb_color_text` varchar(6) NOT NULL,
  `xb_level_name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `x_badges` (`xb_ID`, `xb_requirement`, `xb_requirement_value`, `xb_text`, `xb_text_details`, `xb_icon`, `xb_color`, `xb_color_text`, `xb_level_name`) VALUES
(11, 'added_interaction', 10, 'Added interactions', 'Received more than %d points adding interactions with target companies!', 'search-dollar', 'cc8e34', '', 'Bronze'),
(15, 'added_target', 10, 'Added targets', 'Received more than %s points adding target companies!', 'dollar-sign', 'cc8e34', '', 'Bronze'),
(16, 'asked_question', 10, 'Asked questions', 'Received more than %s points asking questions!', 'question', 'cc8e34', '', 'Bronze'),
(17, 'notified_colleagues_interaction', 10, 'Interactions shared with colleagues', 'Received more than %s points tagging colleagues in interactions added!', 'bullhorn', 'cc8e34', '', 'Bronze'),
(18, 'reply_accepted', 10, 'Helpful replies', 'Received more than %s points thanks to replies marked as helpful!', 'check-double', 'cc8e34', '', 'Bronze'),
(19, 'reply_question', 10, 'Replies to questions', 'Received more than %s points replying to questions!', 'comments', 'cc8e34', '', 'Bronze'),
(20, 'updated_info', 10, 'Updated target profiles', 'Received more than %s points updating profiles of targets!', 'funnel-dollar', 'cc8e34', '', 'Bronze'),
(21, 'updated_wiki', 10, 'Updated wiki', 'Received more than %s points updating profiles of Group companies!', 'pen-nib', 'cc8e34', '', 'Bronze'),
(22, 'upvote_question', 10, 'Helpful questions', 'Received more than %s points for having own questions upvoted!', 'grin-alt', 'cc8e34', '', 'Bronze'),
(23, 'added_interaction', 100, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'aaa9ad', '', 'Silver'),
(24, 'added_interaction', 500, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'ffd700', '495057', 'Gold'),
(25, 'updated_wiki', 1000, 'Updated Bertelsmann wiki', 'Received more than %s points asking questions!', 'pen-nib', 'b9f2ff', '495057', 'Diamond'),
(26, 'added_interaction', 1000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'b9f2ff', '495057', 'Diamond'),
(27, 'added_interaction', 5000, 'Added interactions', 'Received more than %s points adding interactions with target companies!', 'search-dollar', 'e0115f', '', 'Ruby');



CREATE TABLE `x_badges_earned` (
  `xbe_ID` int(11) NOT NULL,
  `xbe_uID` int(11) NOT NULL,
  `xbe_bID` int(11) NOT NULL,
  `xbe_timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `x_badges_earned` (`xbe_ID`, `xbe_uID`, `xbe_bID`, `xbe_timestamp`) VALUES
(19, 1, 11, '2020-03-23 15:24:54'),
(20, 1, 15, '2020-03-23 15:24:54'),
(21, 1, 16, '2020-03-23 15:24:54'),
(22, 1, 17, '2020-03-23 15:24:54'),
(23, 1, 18, '2020-03-23 15:24:54'),
(24, 1, 19, '2020-03-23 15:24:54'),
(25, 1, 23, '2020-03-23 16:00:51'),
(26, 1, 20, '2020-03-23 15:32:31'),
(27, 1, 21, '2020-03-23 15:32:31'),
(28, 1, 22, '2020-03-23 15:32:31'),
(29, 10, 25, '2020-03-23 15:37:32');

db<>在这里摆弄

标签： mysql

解决方案

(xb_icon, xb_requirement_value)除了值对（颜色是次要的，时间戳不保证排序）之外，我还没有找到任何明确标记的识别表达式。

所以解决方案可以是：

SELECT *
FROM x_badges xb
JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
JOIN ( SELECT xb.xb_icon, MAX(xb.xb_requirement_value) xb_requirement_value
       FROM x_badges xb
       JOIN x_badges_earned xbe ON xb.xb_ID = xbe.xbe_bID
       WHERE xbe.xbe_uID = @user
       GROUP BY xb_icon ) max_earned ON (xb.xb_icon, xb.xb_requirement_value) 
                                      = (max_earned.xb_icon, max_earned.xb_requirement_value)
WHERE xbe.xbe_uID = @user;

小提琴

作为建议 - 规范化x_badges表，将其划分为 2 个单独的表 - 一个存储徽章类型数据，另一个存储与第一个表相关的级别相关数据。

推荐阅读