php - 从其他表更新用户分数

Question

我有几个表，每个表都有自己的每个用户的分数。我想创建一个触发器，将每个用户的所有这些分数相加，并将它们放入用户表中名为“分数”的字段中。

Tables (They essentially have the same fields with a few different ones) :

Table 1 : {id, user_id, score}
Table 2 : {id, user_id, score}
Table 3 : {id, user_id, score}

users : {id, name, overall_score} 

//总体 _score 已经有一个值，所以我只想将其他表中的分数字段添加到此表中。

Answer 1

要实现此目的，我们首先编写选择查询并从 3 个给定表中获取每个用户的所有分数总和，这就是实现的方法

select u.*, y.total from users u
left join
(
  select user_id,sum(score) as total from(
    select user_id, score from table_1
    union all
    select user_id, score from table_2
    union all
    select user_id, score from table_3
  )x group by x.user_id
)y on y.user_id = u.id

这是演示 http://www.sqlfiddle.com/#!9/6f936/1

现在让我们将选择转换为更新命令，它将如下

update users u
left join
(
  select user_id,sum(score) as total from(
    select user_id, score from table_1
    union all
    select user_id, score from table_2
    union all
    select user_id, score from table_3
  )x group by x.user_id
)y on y.user_id = u.id
set u.overall_score = coalesce(y.total,0)

这是演示 http://www.sqlfiddle.com/#!9/c6993/1