现有一张请假表:
字段有,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
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
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;
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
谢谢
SELECT 'XS' AS BZ,* FROM 学生表
UNION ALL
SELECT 'JS' AS BZ,* FROM 老师表) A
LEFT JOIN 请假表 B
ON A.student_id=B.user_id
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函数进行数值替换,解决上面的问题
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 希望能帮到你!
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;