mysql 子查询错误 'where clause' 中未知列'tv.last_time'

Question

当我使用mysql进行子查询时，我在'where子句'中得到未知列'tv.last_time'，我该怎么办？在进行子查询时，我想使用外部条件来筛选出不合格的记录

SELECT
info_topic.*, b.newMessage
FROM
info_topic
LEFT JOIN `info_topic_visit` AS tv ON tv.topic_id = info_topic.id
AND tv.user_id = 225
LEFT JOIN (
SELECT
p.topic_id,
count(*) AS newMessage
FROM
info_post p
WHERE
p.create_time > tv.last_time
GROUP BY
p.topic_id
) AS b ON b.topic_id = info_topic.id
ORDER BY b.newMessage DESC

数据库模式：

CREATE TABLE `info_topic` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'topic name',
`summary` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'summary`',
`create_time` datetime DEFAULT NULL COMMENT '',
`sort` int(11) DEFAULT '0' COMMENT '',
PRIMARY KEY (`id`),
);
CREATE TABLE `info_topic_visit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(11) DEFAULT '0' COMMENT '',
`user_id` int(11) DEFAULT '0' COMMENT '',
`last_time` datetime DEFAULT NULL COMMENT '',
PRIMARY KEY (`id`)
);

Answer 1

子查询的问题是tv别名在那里没有意义，因此您不能引用该别名对应的表中的任何内容。一种方法是将您的子查询转换为select子句中的相关子查询：

SELECT
i.*,
(SELECT COUNT(*) FROM info_post p
WHERE p.create_time > tv.last_time AND p.topic_id = i.id) AS newMessage
FROM info_topic i
LEFT JOIN info_topic_visit AS tv
ON tv.topic_id = info_topic.id AND tv.user_id = 225
LEFT JOIN info_topic_member tm
ON i.id = tm.topic_id AND
tm.del_flag = 0    AND
tm.apply_status = i.open_type AND
tm.user_id = 225
WHERE
i.del_flag = 0 AND
i.id > 0       AND
tm.id IS NOT NULL
ORDER BY
newMessage DESC;

还有一种方法可以重新构造查询，这样就不需要使用效率低下的关联子查询。