mysq联表查询

梦里梦外; 2022-10-12 15:53 161阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcHB5X3RlZW1v_size_16_color_FFFFFF_t_70]

select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;
    select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;
    select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;
    
    select * from tbl_dept a right join tbl_emp b on a.id=b.deptId 
    union 
    select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcHB5X3RlZW1v_size_16_color_FFFFFF_t_70 1]

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

![在这里插入图片描述][20210705135852282.png]

select *
      from tbl_dept a
      right join tbl_emp b on a.id= b.deptId
     where a.id is null
     union
    select *
      from tbl_dept a
      left join tbl_emp b on a.id= b.deptId
     where b.deptId is null;

## 连子表 ##

SELECT  a.total1 as total,a.UID from
    (SELECT SUM(`amount`) as total1, `to_userid` as UID
      FROM `t_diamond_cost_log`
     WHERE `user_id`= 1640641
       and `type`= 1
       and `to_userid`<> 1
       and `cdate`> "2019-07-16 00:00:00"
     GROUP BY `to_userid`) as a
    left join
    (SELECT SUM(`amount`) as total2, `user_id` as UID
      FROM `t_diamond_cost_log`
     WHERE `to_userid`= 1640641
       and `type`= 1
       and `user_id`<> 1
       and `cdate`> "2019-07-16 00:00:00"
     GROUP BY `user_id`) as b
     ON a.UID= b.UID

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcHB5X3RlZW1v_size_16_color_FFFFFF_t_70]: /images/20221005/eb843fc830fa466697a2df784a029797.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hhcHB5X3RlZW1v_size_16_color_FFFFFF_t_70 1]: /images/20221005/ec506234e4cd472f90c61f572bea2fc0.png
[20210705135852282.png]: /images/20221005/324d59691b5a418bb76a375057c8a26e.png