左连接三个表,将名称字段组合成一列
问题描述:
我们的数据库中有三种类型的用户。我们正在尝试生成报告。我可以把他们一起加入。但如果我这样做,我有三个名字和三个姓氏列。我如何将所有三种数据合并到一列中。左连接三个表,将名称字段组合成一列
SELECT e_job.objId AS 'Job Number', e_student.Lastname, e_student.Name, e_teachers.Lastname, e_teachers.Name, e_supportStaff.Lastname, e_supportStaff.Name
FROM e_job
LEFT JOIN e_student ON e_job.jName = e_student.username
LEFT JOIN e_teachers ON e_job.jName = e_teachers.username
LEFT JOIN e_supportStaff ON e_job.jName = e_supportStaff.username WHERE jStatus != 2 && jStatus != 7 && jStatus != -1
当前输出显示7列,每种类型的用户都有姓氏的第一个名字。
如何组合最后6列,以便所有名字都在1中,并且最后名称在另一个名称中。
-------------------------------------
|Job Number |Last Name |Fist Name |
|-----------|-----------|-----------|
|1 |Doe |John |
|2 |Test |Test |
|3 |Test 2 |Test2 |
-------------------------------------
谢谢
答
一个通用的解决方案可能是一个联合查询是这样的:
SELECT e_job.objId AS "Job Number", e_student.Lastname AS "Job Number", e_student.Name AS "First Name"
FROM e_job
INNER JOIN e_student ON e_job.jName = e_student.username
UNION
SELECT e_job.objId AS 'Job Number', e_teachers.Lastname, e_teachers.Name
FROM e_job
INNER JOIN e_teachers ON e_job.jName = e_teachers.username
UNION
SELECT e_job.objId AS 'Job Number', e_supportStaff.Lastname, e_supportStaff.Name
FROM e_job
INNER JOIN e_supportStaff ON e_job.jName = e_supportStaff.username WHERE jStatus != 2 && jStatus != 7 && jStatus != -1
注意左联接已改变为内部连接becase的我们就不再需要迎合其他表和内部连接,我们只获取具有匹配数据的行。
的选择,如果你想的类型的人之间进行区分,然后介绍了另一列,并更改使用UNION ALL
SELECT e_job.objId AS "Job Number", 'Student' as Type, e_student.Lastname AS "Job Number", e_student.Name AS "First Name"
FROM e_job
INNER JOIN e_student ON e_job.jName = e_student.username
UNION ALL
SELECT e_job.objId AS 'Job Number', 'Teacher' as Type, e_teachers.Lastname, e_teachers.Name
FROM e_job
INNER JOIN e_teachers ON e_job.jName = e_teachers.username
UNION ALL
SELECT e_job.objId AS 'Job Number', 'Support' as Type, e_supportStaff.Lastname, e_supportStaff.Name
FROM e_job
INNER JOIN e_supportStaff ON e_job.jName = e_supportStaff.username WHERE jStatus != 2 && jStatus != 7 && jStatus != -1
注意:从 UNION,单独使用时,将删除重复的行结果(因此可能比UNION ALL慢,不会删除重复的行)。在我的第二个查询中,你不能复制3个子查询中的任何一行,因为第2列在每个子查询中都是不同的,UNION ALL是有意义的。
它是什么数据库? (这对可用解决方案有所不同)。始终在标签中包含数据库类型。 –
mysql对不起,我没有意识到。 –
**顺便说一句**不要使用单引号标识,请使用它们作为值。例如对** AS“Last Name”**使用双引号**,但是我个人从不使用带空格的列名。 –