php - 如何获取mysql中主结果中两个别名表结果的总和？

Question

我需要一点帮助来简化我的 mysql 查询。我有三张 table 。第一个用于主要用户记录，第二个用于每个用户的奖励积分(每个用户多个记录)，第三个用于结果点(每个用户多个记录)。

查询

SELECT u.*, (SELECT SUM(ub.Points) 
FROM nba_user_bonus AS ub 
WHERE ub.UserId = u.Id) AS BonusPoints,  
(SELECT SUM(ur.Points) 
FROM nba_user_result AS ur 
WHERE ur.UserId = u.Id) AS ResultPoints 
FROM nba_users AS u 
WHERE u.Status = 'Enable' 
ORDER BY u.Id ASC 

在这里，我为每个用户提供了总奖励积分和总结果积分，但我需要按 TotalPoints DESC 为每个用户订单提供 TatalPoint(结果积分和奖励积分之和)。排序上，我想要具有排行榜积分的用户列表。
请询问我是否需要数据库表的更多详细信息。希望得到您的帮助。

Answer 1

您的询问是合理的。您只需将两个值相加即可:

SELECT u.*,
       ((SELECT SUM(ub.Points) FROM nba_user_bonus ub  WHERE ub.UserId = u.Id) + 
        (SELECT SUM(ur.Points) FROM nba_user_result ur WHERE ur.UserId = u.Id)
       ) as TotalPoints
FROM nba_users AS u
WHERE u.Status = 'Enable' 
ORDER BY TotalPoints desc;

如果您需要所有三个值，可以使用子查询或重复计算:

SELECT u.*, (BonusPoints + ResultPoints) as TotalPoints
FROM (SELECT u.*,
             (SELECT SUM(ub.Points) FROM nba_user_bonus ub  WHERE ub.UserId = u.Id) as BonusPoints,
             (SELECT SUM(ur.Points) FROM nba_user_result ur WHERE ur.UserId = u.Id) as ResultPoints
      FROM nba_users AS u
      WHERE u.Status = 'Enable' 
     ) u
ORDER BY TotalPoints desc;

您还可以将其表述为与 group by 的连接。但是，这种在 select 中使用子查询的方法可能比该方法更快。

编辑:

要在第一种情况下处理 NULL，请使用 ifnull() 或 coalesce():

SELECT u.*,
       (coalesce((SELECT SUM(ub.Points) FROM nba_user_bonus ub  WHERE ub.UserId = u.Id), 0) + 
        coalesce((SELECT SUM(ur.Points) FROM nba_user_result ur WHERE ur.UserId = u.Id), 0)
       ) as TotalPoints
FROM nba_users AS u
WHERE u.Status = 'Enable' 
ORDER BY TotalPoints desc;