取的是一个bill_id的动作表中动作为HASTEN的倒数第二条数据select
(select t.action_id
from (
select p.*, rownum rn
from js_ida.t_pub_bill_control_his p
where p.bill_id=a.bill_id
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc
) t
where t.rn=2) 倒数第二条action_id
from js_ida.t_pub_mainbill_his a
where a.bill_id=63918804
报错指向where p.bill_id=a.bill_id
a.bill_id invalid identifier请问第一种写法按照他的格式该怎么改?
(select t.action_id
from (
select p.*, rownum rn
from js_ida.t_pub_bill_control_his p
where p.bill_id=a.bill_id
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc
) t
where t.rn=2) 倒数第二条action_id
from js_ida.t_pub_mainbill_his a
where a.bill_id=63918804
报错指向where p.bill_id=a.bill_id
a.bill_id invalid identifier请问第一种写法按照他的格式该怎么改?
select t.action_id
from (select p.*, rownum rn
from js_ida.t_pub_bill_control_his p,
js_ida.t_pub_mainbill_his a
where p.bill_id=a.bill_id
and a.bill_id= 63918804
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc) t
where t.rn=2;
select --这里没有写出要查询的列
(select t.action_id
from (
select p.*, rownum rn
from js_ida.t_pub_bill_control_his p
where p.bill_id=a.bill_id --这里a应该是某个表的别名,但是找不到它
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc
) t
where t.rn=2) 倒数第二条action_id
from js_ida.t_pub_mainbill_his a
where a.bill_id=63918804
--
不管你的select语句多么复杂,都应该是下面这个结构:
select col_name
from table_name
where condition;
--当然,分组,排序你自己加上
oracle 通过rownum实现分页
(select t.action_id
from (
select p.*, rownum rn
from js_ida.t_pub_bill_control_his p
where p.bill_id=a.bill_id
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc
) t
where t.rn=2) 倒数第二条action_id
from js_ida.t_pub_mainbill_his a
where a.bill_id=63918804
from (
select p.*, rownum rn
from js_ida.t_pub_bill_control_his p
where p.bill_id=a.bill_id
and p.CONTROL_TYPE='HASTEN'
order by p.action_id desc
) t
where t.rn=2) 这个相当于一个表了