left join主表中的数据一条变两条

select s1.*,
s2.*,
n.id as nid
from sec_leave as s1
LEFT JOIN sec_employee as s2 on s1.empid = s2.id
LEFT JOIN sec_noticemessage n on n.knowledge_type=s1.id
where s2.company_id = 1
ORDER BY s1.createDate DESC

上面是有问题的，明显可以看出主表数据因为关联条件有多个变成了多条

解决方法

select s1.*,
s2.*,
n.id as nid
from sec_leave as s1
LEFT JOIN sec_employee as s2 on s1.empid = s2.id
LEFT JOIN (select max(id) id,knowledge_type from sec_noticemessage GROUP BY module) as n on n.knowledge_type=s1.id 
where s2.company_id = 1 
ORDER BY s1.createDate DESC

 

 

当然还有一种方法，和上面不同可以把多的数据显示出来

https://blog.csdn.net/qq_34775102/article/details/83502850

 

如果你觉得这篇内容对你挺有启发，我想邀请你帮我三个忙，让更多的人看到这篇文章：
1、点赞，可以让更多的人看到这篇文章
2、关注我的微信公众号，第一时间阅读我的文章。

3、也欢迎关注我的博客哦