我想连接processID上的两个MySQL表，并将以下内容作为报告输出。
table 1: LMProcess
------------------------------------
| ProcessID | LMNo | Status | Dept |
------------------------------------
|1 |1001 |1 |Legal |
|2 |1001 |1 |R&D |
|3 |1001 |1 |D&R |
|4 |1001 |1 |L&M |
|5 |1002 |1 |Legal |
|6 |1002 |1 |R&D |
|7 |1002 |1 |D&R |
|8 |1002 |1 |L&M |
------------------------------------
table 2: Recommendation
-----------------------------------
| ID | ProcessID | Recommendation |
-----------------------------------
| |1 |Yes |
| |2 |No |
| |3 |Yes |
| |4 |Yes |
| |5 |Yes |
| |6 |No |
| |7 |No |
| |8 |Yes |
-----------------------------------
Report: LM File Recommendations by the Departments
----------------------------------
| LMNo | Legal | R&D | D&R | L&M |
----------------------------------
|1001 |Yes |No |Yes |Yes |
|1002 |Yes |No |No |Yes |
----------------------------------
您正在尝试执行一个pivot操作，这是一些其他RDBMS本机支持的操作，但MySQL不支持（因为开发人员认为它真正属于表示层，而不是数据库）。
但是，您可以按LMNo对结果进行分组，并使用MySQL的GROUP_CONCAT()函数来实现所需的结果：
SELECT LMNo
, GROUP_CONCAT(IF(Dept='Legal', Recommendation, NULL)) AS `Legal`
, GROUP_CONCAT(IF(Dept='R&D' , Recommendation, NULL)) AS `R&D`
, GROUP_CONCAT(IF(Dept='D&R' , Recommendation, NULL)) AS `D&R`
, GROUP_CONCAT(IF(Dept='L&M' , Recommendation, NULL)) AS `L&M`
FROM LMProcess JOIN Recommendation USING (ProcessID)
GROUP BY LMNo