mysql left join on条件与where条件 统计报表查询方法

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

 补充

#补充:最大的ID来关联记录(子查询实现)
where tt.id = (SELECT MAX(id) FROM tbl_goods WHERE order_no = tt.order_no)
  and tt.`order_no` in 
  (   );

#补充:左连接查询,left join on ,在on的条件里面限制左连接的表记录
left join tbl_goods t5 on t1.`order_no` = t5.`order_no` and t5.id = (select max(id) from tbl_goods where `order_no` =t1.order_no)

 

posted on 2024-12-05 18:52  oktokeep  阅读(130)  评论(1)    收藏  举报