如何编写关联Job与jobEmp表的SQL行转列查询？

本次需求核心是将Job表的基础信息，和jobEmp表中按角色关联的员工信息做横向展开，把原本多行存储的同Job不同角色（manager/lead/dev）的员工记录，转换为固定列输出。

实现注意事项 #

• 员工全名拼接需兼容空值场景：针对示例中存在的lastname为空的记录（如empid=5），拼接后需要使用TRIM函数去除多余空格，避免生成带尾部空格的无效值
• 关联逻辑需避免笛卡尔积：不可直接全量关联Job和jobEmp表后不做拆分过滤，否则会出现大量重复的Job行
• 角色匹配严格对应映射规则：jobrole=1对应manager列、jobrole=2对应Lead列、jobrole=3对应dev列

方案1：单角色固定单员工场景 #

如果单个Job下每个固定角色仅对应1名员工，可通过对jobEmp按角色拆分多次左连接的方式实现，代码如下：

SELECT
  j.JobID AS jobId,
  j.jobTitle,
  j.jobType,
  TRIM(CONCAT(je_mgr.firstname, ' ', je_mgr.lastname)) AS manager,
  TRIM(CONCAT(je_lead.firstname, ' ', je_lead.lastname)) AS Lead,
  TRIM(CONCAT(je_dev.firstname, ' ', je_dev.lastname)) AS dev
FROM Job j
LEFT JOIN jobEmp je_mgr
  ON j.JobID = je_mgr.jobid
  AND je_mgr.jobrole = 1
LEFT JOIN jobEmp je_lead
  ON j.JobID = je_lead.jobid
  AND je_lead.jobrole = 2
LEFT JOIN jobEmp je_dev
  ON j.JobID = je_dev.jobid
  AND je_dev.jobrole = 3

方案2：单角色多员工场景（通用推荐） #

结合业务规则中“单个Job可关联多名员工”的设定，同角色可能存在多条员工记录，使用分组聚合+条件判断的写法稳定性更高，不会因同角色多记录导致结果集出现重复Job行，代码如下：

SELECT
  j.JobID AS jobId,
  j.jobTitle,
  j.jobType,
  MAX(CASE WHEN je.jobrole = 1 THEN TRIM(CONCAT(je.firstname, ' ', je.lastname)) END) AS manager,
  MAX(CASE WHEN je.jobrole = 2 THEN TRIM(CONCAT(je.firstname, ' ', je.lastname)) END) AS Lead,
  MAX(CASE WHEN je.jobrole = 3 THEN TRIM(CONCAT(je.firstname, ' ', je.lastname)) END) AS dev
FROM Job j
LEFT JOIN jobEmp je
  ON j.JobID = je.jobid
GROUP BY j.JobID, j.jobTitle, j.jobType

补充：如果需要实现示例输出中首字母大写的格式效果（如原始值design转成Design、automated转成Automated），可在对应字段外层添加字符串格式化函数，不影响整体查询结构。

内容的提问来源于stack exchange，提问作者Prachi Verma