Pan_cras
pan_cras
采纳率100%
2016-11-22 02:37 阅读 2.2k
已采纳

sql 联表查询（两张表）

7
SELECT
    teacher_info.teacher_id,
    tearcher_name,
    teacher_number,
    tearcher_type,
    teacher_image,
    teacher_gender,
    teacher_birthday,
    identity_number,
    company,
    technical_title,
    technical_grade,
    major_type_id,
    major_id,
    courses_offer,
    phone,
    email,
    relative_material,
    LEVEL,
    country,
    province,
    city,
    region
FROM
    teacher_info,
    teacher_region_info
WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id

上面的语句会让teacher_region_info表中如果没有对应的teacher_id就使teacher_info的数据也不显示出来了，如何更改代码？

找到了答案：

SELECT
    teacher_info.teacher_id,
    tearcher_name,
    teacher_region_info.city
FROM
    teacher_info,
    teacher_region_info
WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id
UNION
    SELECT
        teacher_info.teacher_id,
        tearcher_name,
        teacher_region_info.city
    FROM
        teacher_info,
        teacher_region_info
    WHERE
        teacher_info.teacher_id != NULL
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

7条回答 默认 最新

  • 已采纳
    Zhanjr zhanjr 2016-11-22 02:53

    这时候需要用到外连接
    把from后面的改成
    from teacher_info left join teacher_region_info on teacher_info.teacher_id = teacher_region_info.teacher_id
    SQL的四种连接-左外连接、右外连接、内连接、全连接 建议楼主可以去学一下 很有用

    点赞 评论 复制链接分享
  • qq_28342873 没有伞就要努力奔跑 2016-11-22 03:11

    SELECT
    teacher_info.teacher_id,
    tearcher_name,
    teacher_number,
    tearcher_type,
    teacher_image,
    teacher_gender,
    teacher_birthday,
    identity_number,
    company,
    technical_title,
    technical_grade,
    major_type_id,
    major_id,
    courses_offer,
    phone,
    email,
    relative_material,
    LEVEL,
    country,
    province,
    city,
    region
    FROM
    teacher_info T1 LEFT JOIN teacher_region_info T2 ON T1.teacher_id = T2.teacher_id

    点赞 1 评论 复制链接分享
  • Zhanjr zhanjr 2016-11-22 03:23

    WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id(+)

    点赞 1 评论 复制链接分享
  • Zhanjr zhanjr 2016-11-22 03:27

    WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id(+)

    点赞 1 评论 复制链接分享
  • u013181058 圣-雄霸天下 2016-11-22 02:52
     select *
 from teacher_info
 left join teacher_region_info on teacher_region_info.teacher_id=teacher_info.teacher_id
    点赞 评论 复制链接分享
  • code_tai code_tai 2016-11-22 02:57

    现在不是已经实现了么？

    点赞 评论 复制链接分享
  • andy_2016 andy_2016 2016-11-22 03:10

    lz是不是没有把需求搞清楚？

    点赞 评论 复制链接分享
提交

相关推荐