select min(t.record_id) as record_id, t.record_engine
into temp1 --这句为啥出错出错
from tbl_engine_record t
group by t.record_engine, t.record_idselect * from tbl_engine_record t where t.record_id not in(select record_id from temp1)
into temp1 --这句为啥出错出错 ??????????create table TBL_ENGINE_RECORD
(
RECORD_ID INTEGER not null,
RECORD_PRODUCE DATE,
RECORD_SO VARCHAR2(20),
RECORD_ENGINE VARCHAR2(20),
RECORD_REDO VARCHAR2(100),
RECORD_REMARK VARCHAR2(2000),
RECORD_PERSON VARCHAR2(20),
RECORD_DISCARD DATE,
RECORD_SELL DATE,
RECORD_STARTDATE DATE,
RECORD_REPAIR VARCHAR2(20),
RECORD_VEHICLE VARCHAR2(100),
RECORD_OEM VARCHAR2(100),
)alter table TBL_ENGINE_RECORD
add primary key (RECORD_ID)
using index
tablespace ATPU
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
into temp1 --这句为啥出错出错
from tbl_engine_record t
group by t.record_engine, t.record_idselect * from tbl_engine_record t where t.record_id not in(select record_id from temp1)
into temp1 --这句为啥出错出错 ??????????create table TBL_ENGINE_RECORD
(
RECORD_ID INTEGER not null,
RECORD_PRODUCE DATE,
RECORD_SO VARCHAR2(20),
RECORD_ENGINE VARCHAR2(20),
RECORD_REDO VARCHAR2(100),
RECORD_REMARK VARCHAR2(2000),
RECORD_PERSON VARCHAR2(20),
RECORD_DISCARD DATE,
RECORD_SELL DATE,
RECORD_STARTDATE DATE,
RECORD_REPAIR VARCHAR2(20),
RECORD_VEHICLE VARCHAR2(100),
RECORD_OEM VARCHAR2(100),
)alter table TBL_ENGINE_RECORD
add primary key (RECORD_ID)
using index
tablespace ATPU
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
你应该写成
insert into temp1
as
select min(t.record_id) as record_id, t.record_engine
from tbl_engine_record t
group by t.record_engine, t.record_id
==========
??不是你解释的那样。
在PLSQL中into 后面的 table_name 应该是一个记录集类型的变量,才可以使多个列值 INTO 到 里。他和实际存在的表概念完全不同。
如果想使用temp1 的先定义一个 record类型,再定义temp1 ,再使用temp1 。
如
TYPE trd1 AS OBJECT
(tbl_engine_record.record_id%type,
tbl_engine_record.record_engine%type);temp1 trd1
(
select t1.键字段 --找出在本表内F1字段相同,但键字段值不是最小的行
from test t1
where exists (select * from test t2 where t2.f1=t1.f1 and t2.键字段<t1.键字段)
);
--SELECT ... INTO NEW_TABLE 是MS SQL SERVER 的写法
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);