mysql left join on条件与where条件 统计报表查询方法
1.确定数据数量,通过简化的sql查找符合条件的订单号列表
2.然后根据需求,添加单个的字段,在这个过程中会添加left join左关联表查询
需要区分两种情况
2.1.可以唯一确定记录的,
(SELECT t3.get_car_addr FROM `delivery_addr` t3 WHERE sub_order_no = (SELECT MAX(sub_order_no) FROM tbl_order WHERE order_no = t1.order_no)) AS '收件地址', (SELECT t3.return_car_addr FROM `delivery_addr` t3 WHERE sub_order_no = (SELECT MAX(sub_order_no) FROM tbl_order WHERE order_no = t1.order_no)) AS '送件地址',
2.2.查询条件会存在多条记录的,需要使用子查询,同时使用聚合函数等汇总。
(SELECT SUM(t10.amt) FROM koukuan_detail t10 WHERE t10.order_no = t1.order_no AND t10.source_code = '001') AS '扣款金额', (SELECT MAX(t10.time) FROM koukuan_detail t10 WHERE t10.order_no = t1.order_no AND t10.source_code = '002') AS '扣款时间'
3.where过滤条件和left join on后的过滤条件的区别
3.1.where后面的过滤条件,比如是某个子表的条件,等于需要满足条件的才会查询出来。这样会不满足第一条:确定数据数量,这样查询的结果会变少。
原则是:where条件的选择数据数量的条件。而不是作为左关联的条件,除非需要满足左关联的表的条件才加上。
3.2.期望的结果:允许某些左关联的表的记录为空,无法关联,但是不影响整体的取数数量。所以在左关联的时候,可以将条件写到left join on的后面。
LEFT JOIN `cashier` t6 ON t1.order_no = t6.order_no AND t6.pay_kind = '1' AND t6.`pay_type` IN ('1','2') AND t6.`is_delete` =0 LEFT JOIN `cashier` t9 ON t1.order_no = t9.order_no AND t9.pay_kind = '2' AND t9.`pay_type` IN ('1','2') AND t9.`is_delete` =0
标签:join,no,查询方法,t1,t10,mysql,条件,WHERE,order From: https://www.cnblogs.com/oktokeep/p/18589191