目的:
根据user表中的stu_tea_no字段值的长度,长度为10即为学生,要到学生表中查学生姓名、性别;长度为5即为教师,要到教师表中查教师姓名、性别
SQL语句:
SELECT f.no1,f.realname,f.nickname,f.pass,f.no2,f.sex,f.no3,f.rolename
FROM (
SELECT u.user_no no1,si.student_name realname,u.user_nick_name nickname,u.user_password pass,u.stu_tea_no no2,si.student_sex sex,u.role_no no3,r.role_name rolename
FROM `user` u,studentinfo si,role r
WHERE u.stu_tea_no=si.student_no and u.role_no=r.role_no
UNION
SELECT u.user_no,ti.teacher_name,u.user_nick_name,u.user_password,u.stu_tea_no,ti.teacher_sex,u.role_no,r.role_name
FROM `user` u,teacherinfo ti,role r
WHERE u.stu_tea_no=ti.teacher_no and u.role_no=r.role_no) as f
order by f.no1 ASC
用UNION将两段查询合并,不能用join
(UNOIN为上下连接,join为左右连接)
查询结果: