甲骨文从乘表中选择最大值

Question

我有这个查询。它不起作用。我需要从 3 个表中的字段中选择 MAX，其中年份 = 2002。这段代码给了我"order_number"：无效的标识符"。但它存在于表格中。怎么了。使用别名时出现相同的错误：

select MAX(ROUND(to_number(replace(substr(order_number,0, INSTR(a.order_number,'-')-1),'.',',')))) from(
SELECT e.order_date FROM employee_trip_orders e
where EXTRACT(YEAR FROM e.order_date)='2020' 
union all
SELECT p.order_date FROM postgraduate_trip_orders p
where EXTRACT(YEAR FROM p.order_date)='2020' 
union all
SELECT s.order_date FROM student_trip_orders s
where EXTRACT(YEAR FROM s.order_date)='2020' 
) a

提前感谢(

Answer 1

您从未在联合子查询中选择order_number，因此 Oracle 告诉您此列在外部选择中不可用。 试试这个版本：

SELECT MAX(ROUND(TO_NUMBER(REPLACE(SUBSTR(order_number, 0, INSTR(order_number, '-') - 1), '.', ','))))
FROM
(
SELECT order_number FROM employee_trip_orders EXTRACT(YEAR FROM order_date) = 2020 UNION ALL
SELECT order_number FROM postgraduate_trip_orders EXTRACT(YEAR FROM order_date) = 2020 UNION ALL
SELECT order_number FROM student_trip_orders EXTRACT(YEAR FROM order_date) = 2020
) t;