mysql - 子查询作为 mysql 的来源

Question

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

  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;