mysql - 加入年份范围内的表时出现意外结果

Question

我正在与 users、user_roles 和 point_standards 合作。用户需要根据自己的角色在一定时间内达到一定的积分。分数是从特定年份完成的类(class)中检索的。检索用户积分不是问题。

对于用户必须达到的某些点，定义了一个标准。这些产品的生产期限为 3 年，且不能重叠。请参阅下面的示例。

这意味着，从 2018 年到 2020 年，具有分配给这些条件的角色的用户需要达到 93 分。

2018 年，用户应获得 31 分 (93/3)。

问题是当用户想要检查与更多标准相对应的年份的标准时。假设用户想要检查 2018 年、2017 年和 2016 年的标准。这意味着:

(93 / 3) + (50 / 3) + (50 / 3) 

当设定这些金额上限时，2018 年、2017 年和 2016 年的标准应该为 65 分。

对于我预期的最终结果，我希望获得所有用户的概览以及特定年份的标准。

在下面的示例中，我尝试检索 2018 年、2017 年和 2016 年单个用户的条件。

SELECT `users`.first_name,
        ps.points

FROM `users`
       # Join the user roles table
       INNER JOIN `user_roles`
         ON `users`.`role_id` = `user_roles`.`id`

       # Get the criteria for 2018, 2017 and 2016
       LEFT JOIN (SELECT id, role_id, COALESCE(points / 3) AS points
                  FROM point_standards
                  WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
                     OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)) ps
         ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC

这会产生符合正确条件的 2 行。

预期结果

在本例中，它应该返回 3 行。这是因为我想查看 2018 年、2017 年和 2016 年的标准。

然后我希望能够对每个用户的这 3 行的分数进行求和。

问题

• 为什么它只返回 2 行而不是 3 行？我选择了 3 年进行查看。
• 如何将这些行数相加，以便获得所有用户的概览？

Answer 1

您不是选择 3 行，而是选择至少包含 2016 年、2017 年、2018 年之一的所有行，并且其中恰好有两行(表中的前两行)。

如果您希望这三种情况中的每一种都有单独的行，则可以为每种情况编写一个子查询并对它们执行UNION ALL。所以不要写

SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
    OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
    OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)

你使用

SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
UNION ALL
SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
UNION ALL
SELECT id, role_id, COALESCE(points / 3) AS points
FROM point_standards
WHERE 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)

如果您有一个从中获取年份的表，则可以改为与该表执行联接，这会稍微不那么冗长。

编辑:整个查询应该如下所示:

SELECT `users`.first_name,
        ps.points

FROM `users`
    # Join the user roles table
    INNER JOIN `user_roles`
        ON `users`.`role_id` = `user_roles`.`id`

    # Get the criteria for 2018, 2017 and 2016
    LEFT JOIN (
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year)
            UNION ALL
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2017 BETWEEN YEAR(start_year) AND YEAR(end_year)
            UNION ALL
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            WHERE 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)
            ) ps
        ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC

或者更短一些(通过创建自己的years子查询):

SELECT `users`.first_name,
        ps.points

FROM `users`
    # Join the user roles table
    INNER JOIN `user_roles`
        ON `users`.`role_id` = `user_roles`.`id`

    # Get the criteria for 2018, 2017 and 2016
    LEFT JOIN (
            SELECT id, role_id, COALESCE(points / 3) AS points
            FROM point_standards
            JOIN (SELECT 2016 AS y UNION SELECT 2017 AS y UNION SELECT 2018 AS y) years
            ON years.y BETWEEN YEAR(start_year) AND YEAR(end_year)
            ) ps
        ON (user_roles.id = ps.role_id)

WHERE users.id = 123
GROUP BY `users`.`id`, ps.id
ORDER BY `points_internal` DESC