mysql与9个表的左连接

Question

因此，情况是，我有一个涉及9个表的查询，我需要编写它，以便即使workorderstates表中的impactid为NULL，它也会返回所有记录。

在下面的查询之前，我注意到我没有得到所有“打开”的结果，因为最初我只有where workorderstates.impactid = impactdefiniton.impactid，并且在workorderstates表中impactid为NULL的情况下，这个条件将不为真，从而消除了应该返回的记录，因为它们实际上是“打开的”。

所以我在下面设计了这个查询，但每次我运行它时它都不会工作。它将返回不唯一的表别名workorder。如果我对表使用别名，它只是在连接中的右表上移动，因为它不是唯一的。有没有人能帮我调整一下查询，让它正常工作？我尝试了许多变体，有趣的是，第二个查询几乎可以工作，但它返回了重复的记录(在本例中，相同记录的四个)

select workorder.workorderid, workorder.siteid, 
 FROM_UNIXTIME(workorder.CREATEDTIME/1000, '%m-%d-%Y %H:%i:%s') as createdate, 
 categoryname, IFNULL(workorderstates.impactid, "No Set") as impactid, 
 IFNULL(impactdefinition.name, "Not Set") as impactname, first_name,
 sdorganization.name, statusname, title 
from workorder,  statusdefinition, sitedefinition, sdorganization, 
 prioritydefinition, categorydefinition, sduser, aaauser, workorderstates 
left Join impactdefinition on workorderstates.impactid = impactdefinition.impactid  
left join workorder on workorder.workorderid = workorderstates.workorderid 
left join workorderstates on workorderstates.statusid = statusdefinition.statusid 
left join workorder on workorder.siteid = sitedefinition.siteid 
left join sitedefinition on sitedefinition.siteid = sdorganization.org_id  
left join workorderstates on workorderstates.categoryid = categorydefinition.categoryid
left join workorder on workorder.requesterid = sduser.userid 
left join sduser on sduser.userid = aaauser.user_id  
where statusname='Open' and workorder.createdtime >= '1352678400000' 
 and sdorganization.name='MAPL' 
order by workorder.workorderid

几乎正常但丑陋的查询(返回重复的记录)：

select workorder.workorderid, workorder.siteid, 
 FROM_UNIXTIME(workorder.CREATEDTIME/1000, '%m-%d-%Y %H:%i:%s') as createdate,  
 categoryname, IFNULL(workorderstates.impactid, "No Set") as impactid, 
 IFNULL(impactdefinition.name, "Not Set") as impactname, first_name, 
 sdorganization.name, statusname, title 
from workorder,  statusdefinition, sitedefinition, sdorganization, 
 prioritydefinition, categorydefinition, sduser, aaauser, workorderstates 
left Join impactdefinition on workorderstates.impactid = impactdefinition.impactid 
where workorder.workorderid = workorderstates.workorderid 
 and workorderstates.statusid = statusdefinition.statusid 
 and workorder.siteid = sitedefinition.siteid 
 and sitedefinition.siteid = sdorganization.org_id 
 and workorderstates.categoryid = categorydefinition.categoryid 
 and  workorder.requesterid = sduser.userid and sduser.userid = aaauser.user_id 
 and statusname='Open' and workorder.createdtime >= '1352678400000' 
and sdorganization.name='MAPL' 
order by workorder.workorderid

你有什么办法让这个查询工作吗？谢谢你们！

Answer 1

我看了你的查询，我认为你对连接和如何编写连接有一些基本的误解。这就像你只是随意猜测语法，而这不是编写代码的方式。

我检查了您的查询并将其转换为SQL-92语法。我不得不对连接条件做出一些推断，所以我不能保证它对于您的应用程序是正确的，但它更接近于合法的查询。

只是我在您的示例中找不到连接到您的prioritydefinition表的任何条件。这很可能是重复行的原因。您正在生成所谓的Cartesian product。

select workorder.workorderid, workorder.siteid, 
 FROM_UNIXTIME(workorder.CREATEDTIME/1000, '%m-%d-%Y %H:%i:%s') as createdate, 
 categoryname, IFNULL(workorderstates.impactid, "No Set") as impactid, 
 IFNULL(impactdefinition.name, "Not Set") as impactname, first_name,
 sdorganization.name, statusname, title 
from workorder
inner join statusdefinition on workorderstates.statusid = statusdefinition.statusid
inner join sitedefinition on workorder.siteid = sitedefinition.siteid
inner join sdorganization on sitedefinition.siteid = sdorganization.org_id
inner join prioritydefinition ...NO JOIN CONDITION FOUND...
inner join categorydefinition on workorderstates.categoryid = categorydefinition.categoryid
inner join sduser on workorder.requesterid = sduser.userid
inner join aaauser on sduser.userid = aaauser.user_id
inner join workorderstates on workorder.workorderid = workorderstates.workorderid
left Join impactdefinition on workorderstates.impactid = impactdefinition.impactid  
where statusname='Open' 
 and workorder.createdtime >= '1352678400000' 
 and sdorganization.name='MAPL' 
order by workorder.workorderid

在编写更多的SQL连接之前，您确实需要一个了解您的应用程序并知道如何编写SQL的人来指导您。

Answer 2

我也重新格式化了您的查询，但使其具有更直观的层次结构，以显示从第一个(左侧)表到它从(右侧)表中获取其详细信息的关系。正如Bill提到的，您有一个额外的表，它不做任何事情，因此您的笛卡尔产品。

现在，如果你被困住了，并且没有其他人可以真正帮助你，这里是左连接与内连接的基本对比。Left-join基本上是说我希望表中的每条记录都放在左边(就像我在前面列出的那样)，而不管在右边找到了记录。如果有匹配，很好，没问题...但如果没有匹配，您的查询仍将运行。

所以，我已经设置为所有的左连接。你可以根据需要改变那些你知道必须一直存在的东西...诸如工作定单是由“用户”输入的。这样你就可以改变。希望这能帮到你。还要看看我是如何从工作订单到工作订单状态和从工作订单状态嵌套的，以获得相应的状态定义和与工作订单状态表关联的其他内容。其他的是与工作指令直接相关的，所以它们是在那个层次上的。

最后一句话。并非所有字段都是table.field引用(为了提高可读性，我将其更改为别名)。限定您的所有字段，以便您和其他试图提供帮助的人，或者在将来阅读您的代码时知道字段的来源，而不仅仅是猜测(它在其中一个表中)

select 
      WO.workorderid, 
      WO.siteid, 
      FROM_UNIXTIME(WO.CREATEDTIME/1000, '%m-%d-%Y %H:%i:%s') as createdate,  
      categoryname, 
      IFNULL(WOS.impactid, "No Set") as impactid, 
      IFNULL(ImpD.name, "Not Set") as impactname, first_name, 
      SDO.name, 
      statusname, 
      title 
   from 
      workorder WO
         LEFT JOIN workorderstates WOS
            ON WO.workorderid = WOS.workorderid 

            LEFT JOIN statusdefinition StatD
               ON WOS.statusid = StatD.statusid 

            LEFT JOIN categorydefinition CatD
               ON WOS.categoryid = CatD.categoryid 

            LEFT JOIN impactdefinition ImpD
               ON WOS.impactid = ImpD.impactid 

         LEFT JOIN sitedefinition SiteD
            ON WO.siteid = SiteD.siteid 

            LEFT JOIN sdorganization SDO
               ON SiteD.siteid = SDO.org_id 
              and SDO.name = 'MAPL' 

         LEFT JOIN sduser U
            ON WO.requesterid = U.userid 

            LEFT JOIN aaauser AU
               ON U.userid = AU.user_id

   where 
          statusname = 'Open' 
      and WO.createdtime >= '1352678400000' 
   order by 
      WO.workorderid