更新时间:2022-09-23 11:23:09
1、插入数据
1
|
insert into co_user(mailbox_id,domain_id,realname,engname,oabshow,showorder,eenumber,gender,birthday,homepage,tel_mobile,tel_home,tel_work,tel_work_ext,tel_group,im_qq,im_msn,addr_country,addr_state,addr_city,addr_address,addr_zip,remark,last_session,last_login,openid,unionid,wx_id) values( '$mailbox_id' , '$domain_id' , 'testdel2' , 'NULL' , '1' , '0' , 'NULL' , 'male' , '0000-00-00' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , 'NULL' , '0' , '0' , '0' );
|
2、查询两个表mailbox_id的差集。
1
|
select * from core_mailbox where mailbox_id not in ( select mailbox_id from co_user);
|
备注(大神方法):
好吧!这个是可以,但是数据多了的话,想到这个查询的逻辑有点受不住
于是再改为下面的这样:
select cu.mailbox_id,cm.mailbox_id co_user as cu left join core_mailbox as cm on cu.mailbox_id = cm.mailbox_id where cm.mailbox_id NULL;
利用了left join的,然后进行对比,并且利用where进行筛选。
后面也在网上找了这条:
SELECT mailbox_id FROM `co_user` left join (select mailbox_id as i from core_mailbox) as t1 on co_user.mailbox_id= t1.i where t1.i is NULL;
概念上与第二条同理。
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
本文转自 sailikung 51CTO博客,原文链接:http://blog.51cto.com/net881004/2054822,如需转载请自行联系原作者