56,803
社区成员
发帖
与我相关
我的任务
分享sql查询-根据字段类型不同来关联不同表
现有一张请假表：
字段有，leave_id， type，reason， user_id， （分别是主键，对象类型，请假原因，请假人id）
---------------------------------------------------------------
学生表：
字段有，student_id， student_name，
教师表：
字段有，teacher_id， teacher_name，
-------------------------------------------------------
现请假表里面有个对象类型字段，当为1时关联学生表，2时关联老师表，
------------------------------------------------------
现在怎么用一条sql语句查询出老师和学生的全部请假数据
查询的结果如下：
姓名--对象类型---请假原因
------------------------------------------------------------
王学生，学生，trghrigir
孙老师，老师，trghrigir
赵老师，老师，trghrigir
李学生，学生，trghrigir
张老师，老师，trghrigir
胡老师，老师，trghrigir
钱学生，学生，trghrigir
黎学生，学生，trghrigir
字段有，leave_id， type，reason， user_id， （分别是主键，对象类型，请假原因，请假人id）
---------------------------------------------------------------
学生表：
字段有，student_id， student_name，
教师表：
字段有，teacher_id， teacher_name，
-------------------------------------------------------
现请假表里面有个对象类型字段，当为1时关联学生表，2时关联老师表，
------------------------------------------------------
现在怎么用一条sql语句查询出老师和学生的全部请假数据
查询的结果如下：
姓名--对象类型---请假原因
------------------------------------------------------------
王学生，学生，trghrigir
孙老师，老师，trghrigir
赵老师，老师，trghrigir
李学生，学生，trghrigir
张老师，老师，trghrigir
胡老师，老师，trghrigir
钱学生，学生，trghrigir
黎学生，学生，trghrigir
...全文
请发表友善的回复…
发表回复
rick-he 2016-01-30
- 打赏
- 举报
头像不错哦
撞树 2016-01-29
- 打赏
- 举报
SELECT student.student_name AS name,qjtable.type,qjtable.reason FROM student
INNER JOIN qjtable
ON qjtable.type = 1 AND qjtable.user_id = student.student_id
UNION
SELECT teacher.teacher_name AS name,qjtable.type,qjtable.reason FROM teacher
INNER JOIN qjtable
ON
qjtable.type = 2 AND qjtable.user_id = teacher.teacher_id;
百里修行 2016-01-28
- 打赏
- 举报
我个人实现上述业务的2中写法
1.(
SELECT
t2.student_name name,
'学生' type,
t1.reason
FROM
`leave` t1
LEFT JOIN student t2 ON t1.user_id = t2.student_id
WHERE
1 = 1
AND t1.type = 1
)
UNION ALL
(
SELECT
t2.teacher_name name,
'老师' type,
t1.reason
FROM
`leave` t1
LEFT JOIN teacher t2 ON t1.user_id = t2.teacher_id
WHERE
1 = 1
AND t1.type = 2
)
2.SELECT
iF(
type = 1,
(
SELECT
student_name
FROM
student
WHERE
student_id = v.user_id
),
(
SELECT
teacher_name
FROM
teacher
WHERE
teacher_id = v.user_id
)
) as name,
IF (type = 1, '学生', '老师') as type,
v.reason
FROM
`leave` v 希望能帮到你！
「已注销」 2016-01-28
- 打赏
- 举报
select
decode(type,1,(select student_name from 学生表 where student_id=v.user_id),
2,(select teacher_name from 教师表 where teacher_id=v.user_id)) 姓名,
decode(type,1,'学生',2,'老师') 对象类型,
v.reason 请假原因
from 请假表 v
现在可以使用decode函数进行数值替换，解决上面的问题
九月茅 2014-09-02
- 打赏
- 举报
SELECT
CASE WHEN t1.type=1 THEN
(SELECT student_name FROM `学生表` t2 WHERE t2.student_id=t1.user_id)
WHEN t1.type=2 THEN
(SELECT teacher_name FROM `学生表` t2 WHERE t2.teacher_id=t1.user_id)
ELSE END AS '姓名',
CASE WHEN t1.type=1 THEN '学生' WHEN t1.type=2 THEN '老师' ELSE END AS '对象类型',
t1.reason AS '请假原因',
t1.
FROM `请假表` t1;
ACMAIN_CHM 2014-09-02
- 打赏
- 举报
select
if(type=1,(select student_name from 学生表 where student_id=v.user_id),(select teacher_name from 教师表 where teacher_id=v.user_id)) as `姓名`
if(type=1,'学生','老师') as `对象类型`
v.reason as `请假原因`
from 请假表 v
WWWWA 2014-09-02
- 打赏
- 举报
SELECT * FROM (
SELECT 'XS' AS BZ,* FROM 学生表
UNION ALL
SELECT 'JS' AS BZ,* FROM 老师表) A
LEFT JOIN 请假表 B
ON A.student_id=B.user_id
xgia7 2014-09-02
- 打赏
- 举报
select
if(typee=1,
(select student_name from t_student where student_id=v.user_id),
(select teacher_name from t_teacher where teacher_id=v.user_id)
) as namee,
if(typee=1,'studentType','teacherType') as objecttype,
v.reason as reasonn
from t_leave v
谢谢
