多表查询SQL优化

多表连接LEFT JOIN 

是一个例子：三张表，也就是多表联查，使用聚集函数SUM，用到了GROUP BY

select C.channel_short_nameu.user_name,(bs.settlement_amount - bs.received_amount)trans,bs.bill_source_id From bill_source bs 
LEFT JOIN channel C ON bs.channel_id = C.channel_id
LEFT JOIN "user" u ON C.channel_id = u.channel_id

where bs.channel_id = C.channel_id and  bs.ins_date >= '20005-01-01' and bs.ins_date <= '2006-12-31'

GROUP BY C.channel_short_name,u.user_name,trans
ORDER BY trans desc

上边的sql是之前没有优化的时候，相当于 a b c 三个表，channel表为中间表，user表既有channel_id，而bs表也有channel_Id，所以直接用a表关联b表，b表关联c表是常用的套路，而下面的直接用left join  左连接a关联c表，同时左连接b表之后再加入group by、order by 元素。

select C.channel_short_name,u.user_name,(bs.settlement_amount - bs.received_amount)trans From bill_source bs 
LEFT JOIN "user" u  ON bs.channel_id = u.channel_id, channel C
where bs.channel_id = C.channel_id and  bs.ins_date >= '20005-01-01' and bs.ins_date <= '2006-12-31'
GROUP BY C.channel_short_name,u.user_name,trans
ORDER BY trans desc

之前我写的文章有左连接、右链接，内连接的区别，不懂可以去翻阅看一下