mysql - 计算类别和子类别组合的分数

Question

我目前正在使用一个积分系统，该系统使用下面的查询来列出谁在一个类别或其正下方的任何类别中拥有最多的积分(使用“category_relations 表”中的“links_to”和“links_from”)。

SELECT  u.name, 
        pa.user_id, 
        SUM(IF(pa.plus = '1', pa.points_amount, 0)) - 
        SUM(IF(pa.plus = '0', pa.points_amount, 0)) AS points
FROM    points_awarded pa, Users u
WHERE   u.user_id = pa.user_id AND 
        (
           category_id = '" . $category_id . "' OR 
           category_id IN
               (
                   SELECT links_to
                   FROM category_relations
                   WHERE links_from = '" . $category . "'
               )
        ) 
GROUP BY user_id 
ORDER BY points DESC 
LIMIT 50

我现在要做的是将此查询切换为查找特定用户的所有类别，而不是查找特定类别的所有用户。下面是我尝试使用的查询，但这只包括类别的点，而不是直接在下面的子类别。

SELECT   u.name, 
         pa.user_id, 
         pa.category_id,
         SUM(IF(pa.plus = '1', pa.points_amount, 0)) - 
         SUM(IF(pa.plus = '0',pa.points_amount, 0)) AS points
FROM     points_awarded pa, Users u
WHERE    u.user_id = pa.user_id AND 
         u.user_id = '" . $user_id . "'
GROUP BY pa.category_id 
ORDER BY points DESC 
LIMIT 50

Answer 1

解决方案

从您的示例查询中，我能够简化您的表结构，这为我提供了足够的信息来回答您的问题。您可以使用此解决方案:

SELECT   a.user_id, 
         a.name, 
         b.category_id,
         SUM(IF(d.plus = '1', d.points_amount, 0)) -
         SUM(IF(d.plus = '0', d.points_amount, 0)) AS points
FROM     Users a
JOIN     points_awarded b ON a.user_id = b.user_id
JOIN     (
         SELECT links_from, links_to 
         FROM   category_relations 
         UNION
         SELECT links_from, links_from
         FROM   category_relations 
         ) c ON b.category_id = c.links_from
JOIN     points_awarded d ON c.links_to = d.category_id
WHERE    a.user_id = $user_id
GROUP BY a.user_id,
         a.name,
         b.category_id
ORDER BY points DESC
LIMIT    50

其中 $user_id 是查询的用户 ID 参数。

---

分割

基本上，此查询的关键组成部分是我们选择 category_relations 表的方式。

由于您想要子类别点数的总和(按每个父类别)加上它们各自父类别的点数，我们可以通过 垂直添加父类别UNION 并本质上使其成为自身的子类别。这将很好地纳入 GROUP BY 和 SUM。

假设我们有一些这样的(简化的)数据:

Users
------------------
user_id  |  name
433      |  Zane

---

points_awarded
------------------
user_id  |  category_id  |  plus  |  points_amount
433      |  1            |  1     |  785
433      |  2            |  1     |  871
433      |  3            |  1     |  236
433      |  4            |  0     |  64
433      |  5            |  0     |  12
433      |  7            |  1     |  897
433      |  8            |  1     |  3
433      |  9            |  0     |  48
433      |  10           |  1     |  124
433      |  14           |  0     |  676

---

category_relations
------------------
links_from | links_to
1          | 2
1          | 3
1          | 4
5          | 8
5          | 9
7          | 10
7          | 14

---

为了区分父类别和子类别，查询使用 category_relations 表中的两个字段对 points_awarded 表进行自交叉引用连接。

如果我们只是加入 category_relations 表而没有 UNION，我们得到的加入看起来像(列简化):

points_awarded ⋈ (links_from) category_relations ⋈ (links_to) points_awarded:

category_id  |  points_amount  |  category_id  |  points_amount
1            |  785            |  2            |  871
1            |  785            |  3            |  236
1            |  785            |  4            |  64
5            |  12             |  8            |  3
5            |  12             |  9            |  48
7            |  897            |  10           |  124
7            |  897            |  14           |  676

如您所见，最左边的 category_id 是父类别，最右边的 category_id 是各自的子类别。我们可以轻松地对第一个 category_id 和 SUM 第二个 points_amount...

进行分组

等等，我们还需要包含父类别的points_amount!我们如何将第一个 points_amount 明确地变成第二个 points_amount？这就是 UNION 发挥作用的地方。

在我们执行我们的自交叉引用连接之前，我们子选择 category_relations 表以稍微改变它:

SELECT links_from, links_to FROM category_relations
UNION
SELECT links_from, links_from FROM category_relations

然后导致:

category_relations (subselected)
------------------
links_from | links_to
1          | 1       <-- parent category
1          | 2
1          | 3
1          | 4
5          | 5       <-- parent category
5          | 8
5          | 9
7          | 7       <-- parent category
7          | 10
7          | 14

我们基本上使每个父类别成为其自身的子类别。我们在我们的连接中使用这个结果，然后应该产生:

category_id  |  points_amount  |  category_id  |  points_amount
1            |  785            |  1            |  785
1            |  785            |  2            |  871
1            |  785            |  3            |  236
1            |  785            |  4            |  64
5            |  12             |  5            |  12             
5            |  12             |  8            |  3
5            |  12             |  9            |  48
7            |  897            |  7            |  897            
7            |  897            |  10           |  124
7            |  897            |  14           |  676

那里，现在父类别被分解到 SUM 中，我们现在可以对第一个 category_id，对第二个 points_amount 求和(此时忽略第一个 points_amount，因为它无关紧要)，为您提供所需的输出。