列的MySQL UPDATE值,并用旧值添加新值


MySQL UPDATE Value of column and ADD new value with old value

我有一个页面,显示所有在系统中注册的用户的排行榜分数,该分数存储在我的数据库中。

表1

Points_hisotry表:

| points_id || user_id(fk) || point_hist |
|___________||_____________||____________|
|      1    ||     10      ||    100     |  
|___________||_____________||____________|
|      2    ||     11      ||     30     |
|___________||_____________||____________|
|      3    ||     11      ||     70     |
|___________||_____________||____________|
|      4    ||     11      ||    200     |

表2

Users表:

| users_id ||  username || firstname  || lastname ||
|__________||___________||____________||__________||
|    10    ||   alan1   ||    Alan    ||  Smith   ||
|__________||___________||____________||__________||
|    11    ||   Jaz12   ||    Jass    ||  Hopal   ||
|__________||___________||____________||__________||
|    12    ||   Shubs   ||    shubs   ||  hawash  ||
|__________||___________||____________||__________||
|    13    ||   John    ||    Rob     ||  engli   ||

points_history表中,我有3行具有相同的users_id,我需要将它们相加,所以我最终得到了users_id的总Point_hist,应该加起来300。

我需要一个查询来帮助我加入这些表,然后将同一users_id的行相加为一,并将其打印在记分板上。

我试过很多问题,但都没有答对。

以下是排行榜上我的一些PHP。PHP:

$sql = "SELECT * FROM users, points_history WHERE users.users_id = points_history.users_id"; 
$user_query = mysqli_query($db_conx, $sql);
    while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {
    $username = $row ["username"];
    $point_hist = $row["point_hist"];

上面的查询是打印出所有的username,以及它们在上面两个表中的点。

我是新手,所以我需要一些帮助。

您可以使用SUM()对它们进行求和(谁会想到呢(,但您需要通过GROUP BY告诉SQL哪些行要求和,哪些行不应该求和。

SELECT users.*, SUM(points_history.point_hist) AS total_points
FROM users
INNER JOIN points_history
    ON users.users_id = points_history.users_id
GROUP BY users_id

应该完成