联接子查询中的外部选择列值? [英] Outer select column value in joined subquery?

问题描述

是否可以在联接的子查询中使用外部选择中的列值?

Is it possible to use a column value from an outer select within a joined subquery?

SELECT table1.id, table2.cnt FROM table1 LEFT JOIN (SELECT COUNT(*) as `cnt` FROM table2 where table2.lt > table1.lt and table2.rt < table1.rt) as table2 ON 1;

这将导致'where子句'中的未知列'table1.lt'".

This results in "Unknown column 'table1.lt' in 'where clause'".

这是数据库转储.

CREATE TABLE IF NOT EXISTS `table1` ( `id` int(1) NOT NULL, `lt` int(1) NOT NULL, `rt` int(4) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `table2` ( `id` int(1) NOT NULL, `lt` int(1) NOT NULL, `rt` int(4) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`id`, `lt`, `rt`) VALUES (1, 1, 4);

INSERT INTO `table2` (`id`, `lt`, `rt`) VALUES (2, 2, 3);

推荐答案

您的内部查询是一个相关的子查询，但是根本看不到table1.这是对MySQL的限制-请参见 MySQL手册-D.3 .子查询限制.大约一半表示:

Your inner query is a correlated subquery, but it cannot see table1 at all. This is a restriction on MySQL - see MySQL Manual - D.3. Restrictions on Subqueries. About half way down it states:

FROM子句中的子查询不能 相关子查询.他们是 物化(执行以产生一个 结果集) 外部查询，因此它们不能是 在外部查询的每一行进行评估.

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

尽管子查询是LEFT JOIN表达式的一部分，但这是FROM子句的一部分.

Although the subquery is part of a LEFT JOIN expression, this is part of the FROM clause.

这种重新调整可能会为您完成工作:

This reformulation might do the job for you:

SELECT table1.id, 
       (SELECT COUNT(*)
        FROM table2
        WHERE table2.lt > table1.lt
        AND table2.rt < table1.rt) AS cnt
FROM table1;

这篇关于联接子查询中的外部选择列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！