select upp.item_total,
(select sum(iva.total_item_value_afs)
from (select sum(item_value_afs) total_item_value_afs
from (select distinct inn.reg_no,inn.tpt_cuo_nam, inn.item_total, inn.item_value_afs
from sigtasad.customs_import_data inn
where inn.reg_no = upp.reg_no and inn.tpt_cuo_nam = upp.tpt_cuo_nam
)
) iva
) total_item_value,
sum(upp.code_tax_amount),
upp.cmp_nam from SIGTASAD.CUSTOMS_IMPORT_DATA upp where upp.reg_no = '38699' and upp.company_tin = '9003247336' group by upp.reg_no, upp.tpt_cuo_nam, upp.cmp_nam, upp.item_total ;
此查询生成的鲍洛错误:
ora-00904:" upp"。" tpt_cuo_nam":无效标识符 00904. 00000-"%s:无效标识符"
尝试连接一个"派生表",而不是使用"选择独特"的复杂"相关子查询"。没有任何示例数据等。这是一个猜测,但看起来更像是这样:
SELECT
upp.reg_no
, upp.tpt_cuo_nam
, upp.cmp_nam
, upp.item_total
, d.total_item_value
, SUM(upp.code_tax_amount)
FROM sigtasad.customs_import_data upp
LEFT JOIN (
SELECT
inn.reg_no
, inn.tpt_cuo_nam
, SUM(iva.total_item_value_afs) total_item_value
FROM sigtasad.customs_import_data inn
GROUP BY
inn.reg_no
, inn.tpt_cuo_nam
) d ON upp.reg_no = d.reg_no
AND upp.tpt_cuo_nam = d.tpt_cuo_nam
WHERE upp.reg_no = '38699'
AND upp.company_tin = '9003247336'
GROUP BY
upp.reg_no
, upp.tpt_cuo_nam
, upp.cmp_nam
, upp.item_total
, d.total_item_value
;