首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Mysql在新查询中使用查询结果

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

Stack Overflow用户
提问于 2016-07-25 10:46:38
回答 1查看 34关注 0票数 0

所以我有两个表,一个叫做points_log,另一个叫做排行榜。

代码语言:javascript
复制
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)

我有以下疑问:

代码语言:javascript
复制
 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并查找,运行以下查询

代码语言:javascript
复制
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、存储桶和总和(点数)。

现在我所拥有的是

代码语言:javascript
复制
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

这样做的问题是,只有当points_log中存在该用户的值时,它才有效。如果没有值,我不确定如何将其设为0。

任何帮助都是非常感谢的!

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-07-25 11:34:43

代码语言:javascript
复制
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函数。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38558997

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档