MySQL -错误值

Question

我试着计算一些数字，因为它失败了，我花了很多时间，然后我试着这样做：

SELECT SUM( 10 *1 ) 
FROM user_achievements
INNER JOIN achievements
WHERE user_achievements.user_id =8

上面写着: 420？？

我试着让它发挥作用：

SELECT SUM((SELECT score_base FROM achievements WHERE id = user_achievements.achievement_id)*((SELECT pixels_multiplier FROM achievements WHERE id = user_achievements.achievement_id)) * achievement_level) * achievement_level FROM user_achievements INNER JOIN achievements WHERE user_achievements.user_id=2

成就: id，levels，pixels_base，score_base，pixels_multiplier

user_achievements: user_id，achievement_id，achievement_level

Answer 1

如果查询返回420，这意味着在聚合之前在结果集中返回42行。

这是您的查询：

SELECT sum(10*1)
FROM user_achievements ua cross join
     achievements a
WHERE ua.user_id = 8;

如果我不得不猜的话，你错过了一个连接：

SELECT sum(10*1)
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 8;

编辑：

这是注释中的查询：

SELECT sum((SELECT score_base
            FROM achievements
            WHERE id = ua.achievement_id
            ) *
            (SELECT pixels_multiplier
             FROM achievements
             WHERE id = ua.achievement_id
            )
           )
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 2;

它甚至不应该解析。聚合中不允许子查询。试试这个：

SELECT sum(a.score_base * a.pixels_multiplier)
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 2;