sql 多表关联

项目中遇到多表关联查询，device_info与device_oper是一对多关系，project_info,branch_info与device_info是一对多关系。多表的查询

SELECT 
  o.*,
  d.deviceName,
  p.projectName,
  b.`branchName`,
  r.`releaseId` 
FROM
  device_oper o
  LEFT JOIN device_info d 
    ON o.deviceId = d.id 
  INNER JOIN project_info p 
    ON d.projectId = p.`id` 
  INNER JOIN branch_info b 
    ON d.`branchId` = b.`id` 
  INNER JOIN release_info r 
    ON d.`releaseId` = r.releaseId

多表关联查询通常会将关联查询结果作为一个表再进行连接查询

SELECT 
  c.deviceName,
  c.deviceType,
  c.projectName,
  c.branchName 
FROM
  device_oper AS a 
  LEFT JOIN 
    (SELECT 
      a.id AS deviceId,
      a.deviceName AS deviceName,
      a.deviceType AS deviceType,
      b.projectName AS projectName,
      c.branchName AS branchName 
    FROM
      device_info AS a 
      LEFT JOIN project_info AS b 
        ON a.projectId = b.id 
      LEFT JOIN branch_info AS c 
        ON a.branchId = c.id) AS c 
    ON a.deviceId = c.deviceId