2.程序sql中bug优化

考察left join和where  ...  and  ...

# 查看数据是否补全，主要查看userid = 1356823用户
select tt1.userid,ifnull(tt3.totalSend,0) as totalSend,ifnull(tt1.tot,0) as total,ifnull(tt2.succ,0) as success,tt3.time as datetime  from (
 select userid,substring(createtime,1,10) as time,ifnull(count(1),0) as tot from gmi_message_user_his_new 
where substring(returntime,1,10)  = '2019-11-05' and status != '0' and userid = '1356823'
 group by userid,substring(createtime,1,10) 
 )tt1
left join
 (
 select userid,substring(createtime,1,10) as time,ifnull(count(1),0) as succ from gmi_message_user_his_new 
where substring(returntime,1,10)  = '2019-11-05' and status = 'DELIVRD' and status != '0' and userid = '1356823'
 group by userid,substring(createtime,1,10) 
 )tt2
 on tt1.userid = tt2.userid and tt1.time = tt2.time  
left join
 (
 select userid,substring(createtime,1,10) as time,ifnull(count(1),0) as totalSend from gmi_message_user_his_new 
where substring(createtime,1,10) >= '2019-11-01' and userid = '1356823'
 group by userid,substring(createtime,1,10) 
 )tt3
on tt1.userid = tt3.userid and tt1.time = tt3.time

 

A left join B与select * from A,B where .. and .. 

后者A，B中的数据，A如果包含的记录为3条，B只包含2条，最后where条件，得到的数据为2条。

前者中如果A表包含记录为3条，B只包含2条，之后得到3条，B中没有的数据为空。

 

如果A中统计的数据为B的总数，比如A为总数，B中为成功数，那么必须要包含所有的数据。必须通过left join去关联所有的数据。