所以我有这个查询(不是用于生产用途,只是一个查询,无论性能如何,都可以从数据库中管理地获取一些数据)

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是表的名称,在前两个子查询中读取为OK。第三个子查询的区别在于它使用的是JOIN ...有什么想法吗?谢谢

最佳答案

不知道为什么要按原样运行该查询。我建议对所有内容都使用join,如果不给定表会产生结果,则建议使用left outer join

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

关于mysql - MySQL无法在子查询中访问别名,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15791331/

10-14 14:46
查看更多