mysql - 非空列在 mysql 中左移

我正在研究 MySQL 数据库。
我有一个表 empreporting ，这个表包含employeeid 和 reportingemployeeid 列

SELECT e3.`ReportingEmployeeId` AS Level0,e2.`ReportingEmployeeId` AS Level1,e1.`ReportingEmployeeId` AS Level2,e1.`EmployeeId` AS Level3
FROM empreporting e1
LEFT JOIN empreporting e2 ON e1.`ReportingEmployeeId` = e2.`EmployeeId`
LEFT JOIN empreporting e3 ON e2.`ReportingEmployeeId` = e3.`EmployeeId`

上面的查询给出以下结果:

级别0 级别1 级别2 级别3

\N\N 379 369

\N 379 484 372

\N\N\N 379

所需的结果格式为:

级别0 级别1 级别2 级别3

379 369\N\N

379 484 372\N

第379话

请任何人帮助我。
提前致谢

普拉卡什

最佳答案

最终书面查询

SELECT
CASE
    WHEN e3.`ReportingEmployeeId` IS NOT NULL THEN e3.`ReportingEmployeeId`+1000000
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NOT NULL THEN e2.`ReportingEmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NOT NULL THEN e1.`ReportingEmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NULL AND e1.`EmployeeId`  IS NOT NULL THEN e1.`EmployeeId`
END AS Level0,
CASE
    WHEN e3.`ReportingEmployeeId` IS NOT NULL THEN e2.`ReportingEmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NOT NULL THEN e1.`ReportingEmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NOT NULL THEN e1.`EmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NULL AND e1.`EmployeeId`  IS NOT NULL THEN NULL
END AS Level1,
CASE
    WHEN e3.`ReportingEmployeeId` IS NOT NULL THEN e1.`ReportingEmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NOT NULL THEN e1.`EmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NOT NULL THEN NULL
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NULL AND e1.`EmployeeId`  IS NOT NULL THEN NULL
END AS Level2,
CASE
    WHEN e3.`ReportingEmployeeId` IS NOT NULL THEN e1.`EmployeeId`
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NOT NULL THEN NULL
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NOT NULL THEN NULL
    WHEN e3.`ReportingEmployeeId` IS NULL AND e2.`ReportingEmployeeId` IS NULL AND e1.`ReportingEmployeeId` IS NULL AND e1.`EmployeeId`  IS NOT NULL THEN NULL
END AS  Level3
FROM empreporting e1
LEFT JOIN empreporting e2 ON e1.`ReportingEmployeeId` = e2.`EmployeeId`
LEFT JOIN empreporting e3 ON e2.`ReportingEmployeeId` = e3.`EmployeeId`

关于mysql - 非空列在 mysql 中左移，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18888954/