Mysql在新查询中使用查询结果

Question

所以我有两个表，一个称为points_log，另一个称为leaderboard。

mysql> describe points_log;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| user_id | int(11) | NO   |     | NULL    |       |
| points  | int(11) | YES  |     | 0       |       |
| date    | date    | NO   |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe leaderboard;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| bucket    | varchar(255) | YES  |     | NULL    |       |
| user_id   | int(11)      | YES  |     | NULL    |       |
| school_id | int(11)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

我有以下查询:

 SELECT leaderboard.user_id FROM leaderboard where 
 leaderboard.bucket=(SELECT bucket FROM leaderboard WHERE leaderboard.user_id=$user_id) AND
 leaderboard.school_id = (SELECT school_id FROM leaderboard WHERE leaderboard.user_id=$user_id)

这将返回带有 user_id 的一行或多行，这些行位于传入 $user_id 的存储桶中。我想要做的是获取所有这些 user_id 并运行以下查询

SELECT sum(points) FROM points_log WHERE user_id=$user_id AND 
date >= (SELECT subdate(curdate(), INTERVAL (weekday(now())) DAY))

问题是第二个查询如果不能保证返回某些内容，因此在它不返回任何内容的情况下，我希望 sum(points) 为 0。我还需要返回 user_id、bucket 和 sum(点)每行。

现在我拥有的是

SELECT leaderboard.user_id,sum(points_log.points) AS points, leaderboard.bucket
        FROM points_log LEFT JOIN leaderboard ON points_log.user_id = leaderboard.user_id
        WHERE points_log.DATE >= (SELECT subdate(curdate(), INTERVAL (weekday(now())) DAY))
        AND leaderboard.bucket=(SELECT bucket FROM leaderboard WHERE leaderboard.user_id=$user_id)
        AND leaderboard.school_id = (SELECT school_id FROM leaderboard WHERE leaderboard.user_id=$user_id)
        GROUP BY USER_ID ORDER BY SUM(points) DESC

问题在于，它仅在该用户的 point_log 中有值时才有效。我不确定如果没有值如何将其默认为 0。

非常感谢任何帮助!

Answer 1

SELECT leaderboard.user_id, COALESCE( sum(points_log.points), 0 )AS points, leaderboard.bucket 
FROM points_log RIGTH OUTER JOIN leaderboard ON points_log.user_id = leaderboard.user_id 
WHERE points_log.DATE >= (SELECT subdate(curdate(), INTERVAL (weekday(now())) DAY)) 
AND leaderboard.bucket=(SELECT bucket FROM leaderboard WHERE leaderboard.user_id=$user_id) 
AND leaderboard.school_id = (SELECT school_id FROM leaderboard WHERE leaderboard.user_id=$user_id) 
GROUP BY USER_ID ORDER BY SUM(points) DESC 

试试这个...注意 Outer Join和 COALESCE功能。