php - SQL 优化，内部 ? (RBAC)

Question

你能告诉我一个更好的选择来做嵌套的选择语句吗？我正在从事 rbac 项目，我需要获得表的特权。现在这段代码工作得很好，但是如果我在表中有很多数据，查询计数就会变大。

$DB_Query_AID = $DB_Cnx->query("SELECT * FROM stackover_link WHERE link_from='123456' AND link_level='0';");
while($DB_DataAID = $DB_Query_AID->fetch()) {
 if(!empty($DB_DataAID['LID'])) {
  $AID = $DB_DataAID['link_to'];
  $DB_Query_BID = $DB_Cnx->query("SELECT * FROM stackover_link WHERE link_from='$AID' AND link_level='1';");
  while($DB_DataBID = $DB_Query_BID->fetch()) {
   if(!empty($DB_DataBID['LID'])) {
    $BID = $DB_DataBID['link_to'];
    $DB_Query_CID = $DB_Cnx->query("SELECT * FROM stackover_link WHERE link_from='$BID' AND link_level='2';");
    while($DB_DataCID = $DB_Query_CID->fetch()) {
     if(!empty($DB_DataCID['LID'])) {
      $CID = $DB_DataCID['link_to'];
      $DB_Query_DID = $DB_Cnx->query("SELECT * FROM stackover_link WHERE link_from='$CID' AND link_level='3';");
      while($DB_DataDID = $DB_Query_DID->fetch()) {
       if(!empty($DB_DataDID['LID'])) {
        //foooooooooooooooo........
       }
      }
     }
    }
   }
  }
 }
}

是否可以只用 1 个查询来做同样的事情？感谢您的帮助。

Answer 1

这看起来像 INNER JOIN 是可能的:

SELECT sl0.*, sl1.* FROM stackover_link sl0 
    INNER JOIN stackover_link sl1 ON sl0.link_to = sl1.link_from and sl1.link_level = 1

...

WHERE sl0.link_from='123456' AND sl0.link_level='0';