关于mysql左连接的一些理解

最新推荐文章于 2024-09-23 17:25:15 发布

永远都是初学者

最新推荐文章于 2024-09-23 17:25:15 发布

阅读量1k

点赞数

分类专栏：踩坑记录 mysql

本文链接：https://blog.csdn.net/weixin_43939597/article/details/93726267

版权

踩坑记录同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

通常我们左连接的时候，会觉得以左表为主，结果查出来的数据条数却比左表的少。原因很简单，因为表连接，你的where子句中加了右表的字段过滤。而有时候根据业务要求，我们需要显示所有的左表数据，怎样在不修改过滤条件的情况下，完全显示左表的所有行呢？这个时候，拿左表再去left join当前的表结果即可。例子如下

`SELECT ram.user_id as user_id,
IFNULL(A.last_days,0) ,
IFNULL(A.read_time,0),
ru.name as name,
ru.username as user_name,
ru.photo_url as photo_url

from r_activity_member ram   LEFT JOIN  
(
SELECT
				count(DISTINCT DATE_FORMAT(rar.end_time,'%Y/%m/%d')) as last_days,
                sum(rar.read_time)/60000 as read_time ,
                rar.user_id as user_id
                FROM
                 r_activity_member ram
								left join r_read_history rar on rar.user_id = ram.user_id
								LEFT JOIN r_user ru on ram.user_id = ru.id

                WHERE
                    rar.is_delete = 0 and ru.is_delete = 0 and ram.is_delete = 0

                    and rar.activity_id = '101'
                    AND (
                        rar.end_time
                        )  between '2019-03-01' and date_sub('2019-06-26', interval -1 day)
                    GROUP BY rar.user_id
                    having  sum(rar.read_time)/60000 >=3
                    order by sum(rar.read_time) desc
) A
on ram.user_id = A.user_id
LEFT JOIN r_user ru on ru.id = ram.user_id
where ram.is_delete = 0  and  ram.act_id = '101'
and ru.is_delete = 0`