Question

oracle 嵌套查询 sql问题

select city.name,city.code,
(
select count(1) from (
select smmma.merchant_no
from sto_m00_customer smc,sto_m04_mer_multi_app smmma
where smc.id = smmma.customer_id(+)
and smc.city = city.code
)
)as val
from sto_c03_city city
where city.province_code = '34'

报错，子查询里面city.code标示符无效，，，什么原因，如何解决！

Answer 1

你好！很高兴为您解答！根据您的问题，回答如下：
别用别名（city）,用全名看看（sto_c03_city）

Answer 2

是不是 sto_m00_customer smc,sto_m04_mer_multi_app 这两个表内都有city.code

追问

不是的，只有 sto_m00_customer smc表有city，,sto_m04_mer_multi_app表是sto_m00_customer smc表的字表

追答

不知道对不对
select city.name,city.code,
(
select count(1) from (
select smmma.merchant_no
from sto_m00_customer smc,sto_c03_city city
where smc.id = customer_id
and smc.city = city.code
and smc.id in (select customer_id from sto_m04_mer_multi_app)

)
)as val
from sto_c03_city city
where city.province_code = '34'