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