关联查询经典案例

例1 场景：有用户表sys_user，用户岗位关联表 sys_user_post ，岗位表sys_post

SELECT * from sys_user t;
SELECT * from sys_user_post t;
SELECT * from sys_post t;

字段如下：

查询用户名称以及该用户的岗位名称信息：

过程如下：

SELECT u.user_name,up.post_id from sys_user u LEFT JOIN sys_user_post up on u.user_id = up.user_id;

SELECT u.user_name,up.post_id,p.post_name from sys_user u LEFT JOIN sys_user_post up on u.user_id = up.user_id LEFT JOIN sys_post p on up.post_id = p.post_id;

结果：

场景2  用户表(学生表、导师表或者员工与上级经理等)是同一张表sys_user，学生与导师关系对应表sys_user_tutor，表结构如下：

要求查询出员工id，员工姓名，与该员工的导师id，导师姓名：

SELECT s.user_id as stuId, s.user_name as stuName,t.user_id as tutorId,t.user_name as tutorName FROM sys_user s LEFT JOIN sys_user_tutor st on s.user_id = st.user_id LEFT JOIN sys_user t on t.user_id = st.tutor_id;

查询结果如下：

常用sql总结：

SELECT * from sys_user t;
SELECT * from sys_user_post t;
SELECT * from sys_post t;
SELECT * from sys_user_tutor t;

SELECT u.user_name,up.post_id from sys_user u LEFT JOIN sys_user_post up on u.user_id = up.user_id;

SELECT u.user_name,up.post_id,p.post_name from sys_user u LEFT JOIN sys_user_post up on u.user_id = up.user_id LEFT JOIN 
sys_post p on up.post_id = p.post_id;

-- 按工号
SELECT * FROM user s LEFT JOIN student_tutor st on s.code = st.stuCode LEFT JOIN user t on st.tutorCode = t.code;
SELECT s.code as stuCode,s.name as stuName,t.code as tutorCode,t.name as tutorName FROM user s LEFT JOIN student_tutor st 
on s.code = st.stuCode LEFT JOIN user t on st.tutorCode = t.code;

-- 按ID
SELECT * FROM user s LEFT JOIN student_tutor st on s.id = st.stuId LEFT JOIN user t on st.tutorId = t.id;
SELECT s.id as stuId, s.name as stuName,t.id as tutorId,t.name as tutorName FROM user s LEFT JOIN student_tutor st 
on s.id = st.stuId LEFT JOIN user t on st.tutorId = t.id;

-- 实战
SELECT s.user_id as stuId, s.user_name as stuName,t.user_id as tutorId,t.user_name as tutorName FROM sys_user s LEFT JOIN 
sys_user_tutor st on s.user_id = st.user_id LEFT JOIN sys_user t on t.user_id = st.tutor_id;