【sql】关联查询+表自关联查询

最新推荐文章于 2023-11-17 18:55:03 发布

weixin_33693070

最新推荐文章于 2023-11-17 18:55:03 发布

阅读量575

点赞数

原文链接：http://www.cnblogs.com/sxdcgaq8080/p/9205462.html

版权

表：

经销商 dealer 字段 uid parent_uid name

联系人 contact 字段 uid dealer_id contact_main

需求：

想要查询到经销商的信息，和本经销商的主要联系人的信息，和本经销商的父级经销商的name

情况：

可能有parent_id是null

sql语句：

    SELECT
        dea.uid uid,
        dea.enabled_flag enabledFlag,
        dea.delete_flag deleteFlag,
        dea.tenement_id tenementId,
        dea.parent_id parentId,
        pd.name parentName,
        dea.name name,
        dea.type type,
        dea.bar_code barCode,
        dea.outer_code outerCode,
        dea.outer_id outerId,
        dea.mne_code  mneCode,
        dea.address address,
        dea.address_xy addressXy,
        dea.business_area businessArea,
        dea.business_area_xy businessAreaXy,
        con.name  contactName,
        con.mobile mobile
    FROM
        dealer AS dea
    LEFT JOIN  (select * from contact where tenement_id = '9f63f84f-52c6-4c8e-b3c3-66b9f1f283ba' and main_contact = 1) con ON dea.uid = con.dealer_id

    LEFT JOIN  dealer pd on pd.uid = dea.parent_id

转载于:https://www.cnblogs.com/sxdcgaq8080/p/9205462.html