mysql left join计算数量_Mysql-多个left join 计算逻辑

单个left join：

(1)一对一：结果表的行数=左表行数

(2)一对多：结果表的行数>左表行数

多个left join：

(0)多个left join由上到下，依次生成查询表，原理同单个left join

(1)需要补充的是，如果在left join a表之前，想先对a表做一个字段的筛选，不能在多个left join之后用where来条件过滤，因为，多个left join是优先于where执行的，如果用where是对多个left join之后的结果查询表进行的条件过滤。(这种很容造成结果错误，也就是结果数据不全)

应该用，left join ON 后+"and"去先过滤

a left join b ON a.id=b.id anda.isdel =0 b.status =1

left join c ON a.id =c.id andc.type=3

PS:有时候，单个left join、多个left join 需要跟select配合套多个"select马甲"使用，下边是一个很不错的常用模版套法：

(一般对于不知道什么时候起别名 什么时候用括号的老铁们 真实头疼  所以发个模板,可以根据自己的需求，进行套用)

https://www.cnblogs.com/LHWorldBlog/p/7753914.html

不多说 直接上语句

SELECT

a.id,

a.thumbNail,

a. NAME,

a.marketPrice,

a.memberPrice,

a.personName,

a. STATUS,

a.recieveOrderDate,

a.trackNumber,

a.contact,

a.reportSendDate,

b.trackNumber,

a.reportDownloadPath

FROM

(

SELECT

od.id,

ps.thumbNail,

ps. NAME,

od.marketPrice,

od.memberPrice,

od.personName,

od. STATUS,

od.recieveOrderDate,

ol.trackNumber,

ol.contact,

od.reportSendDate,

od.reportSendOrderLogisticId,

od.reportDownloadPath

FROM

orders.order_detail od

LEFT JOIN orders.order_logistics ol ON od.recieveOrderLogisticId = ol.id

LEFT JOIN orders.product_snapshot ps ON od.productSnapShotId = ps.id

WHERE

od.valid = TRUE

AND ol.valid = TRUE

AND od.orderId =?

) a

LEFT JOIN (

SELECT

ol.trackNumber,

od.id

FROM

orders.order_detail od

LEFT JOIN orders.order_logistics ol ON od.reportSendOrderLogisticId = ol.id

WHERE

od.valid = TRUE

AND ol.valid = TRUE

AND od.orderId =?

) b ON a.id = b.id