如何在子查询中从主查询传递值

Question

我想将一个变量从主查询传递到子查询中。 这是一个问题，因为此查询使用 3 个子。

我尝试过加入，但由于我是新手，所以有点混乱。 代码是这样的

select fav.cust_id
from 
(
select cust_id
from
(
select cust_id
from
(
select c.cust_id
from customer c
)
)
)fav
where fav.cust_id = 12;

如我们所见，IM 尝试将值"12"传递到最深的子查询 (c.cust_id) 中，以便它在主查询中返回预期值。 如果我尝试在第一个子查询中传递值，它将返回错误的数据，因为它尝试在使用条件之前从第二个子查询获取所有数据。 所以IM试图做的是传递条件在最深的子查询中，以便它将从那里过滤结果以返回预期值。

更新： 这是我所做的接近真实的查询。

select fav.cust_lname, fav.cust_time
from
(
select max(cust_lname), max(cust_time)
--there is another code here for some calculations
from
(
select lname cust_lname, time cust_time
--there is another code here for some calculations
from 
(
select c.cust_id
--there is another code here for some calculations to return the cust_lname and cust_time
from
customer c
where cust_g = 'MALE'
AND cust_id = --in the original code, this is a parameter. i want this to read the value from the main query
)
)       
)fav,
customer_table ct
where ct.header_id = --custom parameter that im trying to play with
AND ct.cust_id = --i want to relate this with the cust_id inside the sub query

Answer 1

查询可以简化如下。不需要嵌套子查询。

select fav.cust_id
from fav
join b
on b.cust_id = fav.cust_id
join c
on c.cust_id = b.cust_id
WHERE fav.cust_id = 12

Answer 2

来自您给定的查询，

(
select max(cust_lname), max(cust_time)
--there is another code here for some calculations
from
(
select lname cust_lname, time cust_time
--there is another code here for some calculations
from 
(
select c.cust_id
--there is another code here for some calculations to return the cust_lname and cust_time
from
customer c
where cust_g = 'MALE'
AND cust_id = --in the original code, this is a parameter. i want this to read the value from the main query
)
)       
)fav

是表内查询(提取的记录用作表1数据)

使用的另一个表是

customer_table ct

根据您的代码，它就像将表1(fav)与第二个表连接在一起，但在子查询中不可用。

考虑一下... 仅用于表1的代码片段....它将从那里获取ct.cust_id???它 ct 表不在子查询中，这将给出无效标识符错误。

根据给定的代码，您尝试获取fav.cust_lname，fav.cust_time通过与外部客户表中的cust_id联接来获取子查询中的值。 如果是这样要求，那么它可以写成

select (subquery with join from ct table) from customer_table ct

如果要在表内查询本身中使用联接

select column1, column2 from (select ....cust_id... from customer_table ctin ...)fav, customer_table ct where...

应该这样做，即应该在表内查询中引用该列调用连接表

最主要的是，您已将表内查询与"表内查询"结合使用，该查询在表内查询中不可用，要么切换到子查询，要么在表内查询中引入外部表

Answer 3

好的，所以我今天学到了新东西。 我使用OUTER APPLY在最深的子查询中传递值，感谢您的所有建议。

select fav.cust_lname, fav.cust_time
from
customer_table ct
outer apply
(
select max(cust_lname), max(cust_time)
--there is another code here for some calculations
from
(
select lname cust_lname, time cust_time
--there is another code here for some calculations
from 
(
select c.cust_id
--there is another code here for some calculations to return the cust_lname and cust_time
from
customer c
where cust_g = 'MALE'
AND cust_id = ct.cust_id --match with the column in the outside query table
)
)       
)fav,
where ct.header_id = --custom parameter that im trying to play with
--AND ct.cust_id = --i want to relate this with the cust_id inside the sub query --removed this