左连接查询的SQL

SELECT
        DISTINCT q.id,
        q.employee_name AS employeeName,
        q.employee_id AS employeeId,
        t.sex AS sex,
        e.weight AS weight,
        e.height AS height,
        t.birthday AS birthday,
        b.qualification AS education,
        t.join_member_time AS joinMemberTime,
        t.department AS crew,
        w.squadron AS squadron,
        d.qualification_name AS qualificationName,
        a.serve_type AS serviceModel,
        c.visa_type AS visaInformation,
        q.speciality AS speciality,
        q.competition_experience AS performanceCompetitionExperience,
        q.etiquette_experience AS etiquetteExperience,
        q.picture AS pictures,
        q.family_background AS familyBackground,
        q.recommendations AS recommendations,
        q.video AS video,
        q.talent_certificate AS talentCertificates
        FROM
        ct_bit q
        LEFT JOIN (SELECT * FROM hr_base_info WHERE delete_flag = 0) t ON q.employee_id = t.employee_id
        LEFT JOIN (SELECT * FROM rm_employee_info WHERE delete_flag = 0 ) e ON t.id_number = e.id_no
        LEFT JOIN (SELECT * FROM hr_education_info WHERE delete_flag = 0 ) b ON q.employee_id = b.employee_id
        LEFT JOIN (SELECT * FROM ct_squadron_info_maintenance WHERE delete_flag = 0 ) w ON q.employee_id = w.employee_id
        LEFT JOIN (SELECT * FROM cr_qualification WHERE delete_flag = 0 AND STATUS != 1 ) d ON d.employee_id =
        q.employee_id
        LEFT JOIN (SELECT * FROM cr_trainman_info WHERE delete_flag = 0 ) a ON q.employee_id = a.employee_id
        LEFT JOIN (SELECT * FROM cr_credentials_info WHERE delete_flag = 0 ) c ON q.employee_id = c.employee_id
        WHERE q.delete_flag = 0 AND q.id = '78fe25d3188b4073a9135134462369f0'  AND q.employee_id = '3000000004'
        order by q.sys_create_dt desc
        limit 1

1.多表查询的时候。经常会用到左连接查询。left join 以左表为主,去右找,找到就查出来。找不到就空着

2.AS起别名

3.distinct去重,返回不重复记录

4.left jion ()on 两个表相同的id

发布了6 篇原创文章 · 获赞 1 · 访问量 92
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览