MySQL 无法访问子查询中的别名

Question

所以我有这个查询(不是用于生产用途，只是一个查询，用于从数据库中获取一些数据，而不考虑性能)

SELECT 
  `guests`.`id`, 
   CONCAT(u.`fname`, '', u.`lname`) "name", 
  `u`.`id` "u", 
   (SELECT COUNT(r.id) FROM reservations r WHERE r.guest_id=guests.id) "reservations", 
   (SELECT COUNT(t.id) FROM tasks t WHERE t.guest_id = guests.id) "tasks", 
   (SELECT COUNT(m.id) FROM scheduled_emails m JOIN reservations r ON (r.guest_id = `guests`.`id` AND m.reservation_id = r.id)) "emails", 
  `guests`.`created` "created", 
  `guests`.`updated` "updated" 
FROM `guests`
JOIN `users` AS u ON u.`id` = `guests`.`user_id` LIMIT 0,25

我明白了

#1054 - Unknown column 'guests.id' in 'on clause'

导致问题的行是第三个子查询:

(SELECT COUNT(m.id) FROM scheduled_emails m JOIN reservations r ON (r.guest_id = `guests`.`id` AND m.reservation_id = r.id)) "emails", 

有人知道为什么它在这里失败了吗？我的意思是我没有使用别名，guests 是表的名称，它在 2 个第一个子查询中读取正常。第三个子查询的不同之处在于它使用的是 JOIN ...有什么想法吗？谢谢

Answer 1

JOIN 直接连接所有表，而不是使用相关子查询执行此操作。像这样:

SELECT 
  g.id, 
  u.name, 
  `u`.`id`                  AS u, 
  g.`created`               AS created, 
  g.`updated`               AS updated,
  COUNT(r.id)               AS reservations, 
  COUNT(t.id)               AS tasks, 
  COUNT(m.id)               AS emails, 
FROM `guests`               AS g
INNER JOIN reservations     AS r  ON r.guest_id = g.id
INNER JOIN tasks            AS t  ON t.guest_id = g.id
INNER JOIN scheduled_emails AS m  ON r.guest_id = g.id
                                 AND r.id       = m.reservation_id 
INNER JOIN
(
   SELECT id, CONCAT(u.`fname`, '', u.`lname`) AS name
   FROM `users`          
)AS u  ON u.id       = g.user_id
GROUP BY g.id, 
         u.id,
         u.name, 
         g.created, 
         g.updated
LIMIT 0, 25;