子查询作为mysql的from

Question

我在这里做错什么了？尝试使用子查询作为my，以确保所有已连接的表都在正确的集合上连接。

  SELECT
    active_users.username as username,
    active_users.computer_name as computer_name,
    alert.cnt as alerts

  FROM
    (SELECT
        computer_name,
        username
      FROM computers

      INNER JOIN users
        on users.computer_id = computers.computer_id

      WHERE computers.account_id = :cw_account_id AND computers.status = :cw_status
    ) AS active_users

    LEFT JOIN
      (SELECT
          user_id,
          count(*) as cnt
        from logs
        group by user_id
      ) AS alert
      on alert.user_id = active_users.user_id

Answer 1

您需要在第一个子查询中选择user_id：

 SELECT active_users.username as username, active_users.computer_name as computer_name,
        alert.cnt as alerts
 FROM (SELECT user_id, computer_name, username
       FROM computers INNER JOIN
            users
            on users.computer_id = computers.computer_id
       WHERE computers.account_id = :cw_account_id AND computers.status = :cw_status
      ) active_users LEFT JOIN
      (SELECT user_id, count(*) as cnt
       from logs
       group by user_id
      ) alert
      on alert.user_id = active_users.user_id;