mysql - 尝试改进sql查询输出

Question

我在创建查询时遇到问题。以下是我的想法:

SELECT sitedata.sitecode       Site, 
       projectdata.startdate   Start, 
       projectdata.enddate     End, 
       resourcedata.firstname  PM_Name, 
       resourcedata.firstname  Lead_Name, 
       rresourcedata.firstname Other_Name 
FROM   projectdata 
       JOIN sitedata 
         ON projectdata.siteid = sitedata.id 
       JOIN resourcedata 
         ON projectdata.pmid = resourcedata.id 
             OR projectdata.leadid = resourcedata.id 
             OR projectdata.otherid = resourcedata.id 
WHERE  startdate = '03/06/2013' 

当前查询的输出如下:

Site   PM_Name   Lead_Name   Other_Name   
ST1    Joe       Joe         Joe
ST1    Jim       Jim         Jim
ST1    Bill      Bill        Bill
ST2    Lee       Lee         Lee
ST2    Jim       Jim         Jim
ST2    Bill      Bill        Bill

下面是我想要得到的输出:

Site   PM_Name   Lead_Name    Other_Name   
ST1    Jim       Joe          Bill
ST2    Bill      Lee          Jim

Answer 1

您需要 3 个单独的 JOINS:

SELECT sitedata.sitecode       Site, 
       projectdata.startdate   Start, 
       projectdata.enddate     End, 
       pm.firstname  PM_Name, 
       ld.firstname  Lead_Name, 
       on.firstname Other_Name 
FROM   projectdata 
       JOIN sitedata ON projectdata.siteid = sitedata.id 
       LEFT JOIN resourcedata pm ON projectdata.pmid = resourcedata.id 
       LEFT JOIN resourcedata ld ON projectdata.leadid = resourcedata.id 
       LEFT JOIN resourcedata on ON projectdata.otherid = resourcedata.id 
WHERE  startdate = '03/06/2013'

我使用 LEFT JOIN 是为了防止某些角色未分配(NULL)，如果您确定情况并非如此，您可以继续使用 INNER JOIN。