select * , row_number() over(order by time) id from audits
谢谢ls的,不过怎样把select的内容放进新的临时表呢
oracle的临时表这样用的 create temporary table tb as SELECT * FROM audits ORDER BY time on commit presever rows; 或 create temporary table tb as SELECT * FROM audits ORDER BY time on commit delete rows;
那么组合起来是不是可以这样写 create temporary table temp_audits as select * , row_number() over(order by time) id from audits
好像语法有问题啊SQL> create global temporary table temp_audits 2 as 3 select *,row_number() over(order by time) id from audits; select *,row_number() over(order by time) id from audits * ERROR at line 3: ORA-00923: FROM keyword not found where expected
现在问题是这样,好像×里面包括零长度的字段,我用其他表是可以成功的,audits会报这个错,那该怎么解决呢SQL> create global temporary table temp_audits 2 on commit preserve rows 3 as 4 select * from audits; select * from audits * ERROR at line 4: ORA-01723: zero-length columns are not allowed
create temporary table temp_audits as (select * , row_number() over(order by time) id from audits)
select * , row_number() over(order by time) id from audits
create temporary table tb
as
SELECT * FROM audits ORDER BY time
on commit presever rows;
或
create temporary table tb
as
SELECT * FROM audits ORDER BY time
on commit delete rows;
create temporary table temp_audits
as
select * , row_number() over(order by time) id from audits
2 as
3 select *,row_number() over(order by time) id from audits;
select *,row_number() over(order by time) id from audits
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected
2 on commit preserve rows
3 as
4 select * from audits;
select * from audits
*
ERROR at line 4:
ORA-01723: zero-length columns are not allowed
create temporary table temp_audits
as
(select * , row_number() over(order by time) id from audits)