我有一个临时表temp,字段为start,end,name
例如 start end name
1 2 sdf
2 3 sdff
1 2 dsf
1 4 sfd
2 3 ds
我想把他们select进入一个新表。条件就是只插入第一次出现的start,end的项
新表的项数为id,start ,end name 。其中id由序列seq产生
上面的最后结果就是
start end name
1 2 sdf
2 3 sdff
1 4 sfd
求这样的一个SQL语句。谢谢!
例如 start end name
1 2 sdf
2 3 sdff
1 2 dsf
1 4 sfd
2 3 ds
我想把他们select进入一个新表。条件就是只插入第一次出现的start,end的项
新表的项数为id,start ,end name 。其中id由序列seq产生
上面的最后结果就是
start end name
1 2 sdf
2 3 sdff
1 4 sfd
求这样的一个SQL语句。谢谢!
select seq.nextval,a.start,a.end,a.name
from temp a,(select start,end,min(rowid) min_rowid from temp group by start,end) b
where a.rowid=b.min_rowid;
其实我的原表为 FirstPath
Start End Name tag
1 2 sdf 0
2 3 gdf 0
1 2 sdf 0
2 3 gdf 1
4 5 dsg 0
3 4 hhd 0
我要将其中tag为0的项,插入我的一个新表,并且新表中不能有重复的项。
新表SecondPath为id start end name (id由序列seq产生)
1 1 2 sdf
2 2 3 gdf
3 4 5 gdf
4 3 4 hhd
不要排序,如何用SQL语句实现?谢谢!
insert into SecondPath
select seq.nextval,a.start,a.end,a.name
from firstPath
where firstpath.tag=0;
删除表中重复记录:
select distinct * into #Tmp from secondPath
drop table secondPath
select * into secondPath from #Tmp
drop table #Tmp
insert into SecondPath
select seq.nextval,start,end,name
from firstPath
where firstpath.tag=0;
删除表中重复记录:
select distinct * into #Tmp from secondPath
drop table secondPath
select * into secondPath from #Tmp
drop table #Tmp
select seq.nextval, Start, end, name,0
from FirstPath
where tag=0
group by Start, end, name
不知道你说的重复的项是什么概念 是 Start End Name三项不重复还是只要
Start End两向不重复
insert into SecondPath(id, start, end, name)
select seq.nextval, start, end, name, tag
from (select start, end, name, tag, row_number()
over(partition by start, end order by rowid) row from FirstPath where tag=0) b
where b.row=1
to zzwind5() start 和end相同就表示是相同项,就不要插入。你的第二个SQL语句报错。ORA-00923: 未找到预期 FROM 关键字
select distinct seq.nextval,start,end,name
from firstPath
where firstpath.tag=0;
CREATE SEQUENCE Seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
Create table SecondPath(
ID number(10) ,
StartPT number ,
Endpt number,
name varchar(16));
Create table firstPath(
StartPT number,
EndPT number,
name varchar(16),
tag number);
insert into firstPath values (1,2,'dgj',0);
insert into firstPath values (2,3,'ud',0);
insert into firstPath values (1,2,'dj',0);
insert into firstPath values (1,2,'dhgj',1);
insert into firstPath values (3,4,'NU',0);
insert into firstPath values (5,6,'ULL',0);
insert into firstPath values (4,5,'NljU',0);
commit;
最后的结果应该是 ID StartPT endPT Name
1 1 2 'dgj'
2 2 3 'ud'
3 3 4 'NU'
4 5 6 'ULL'
5 4 5 'NljU'
SQL> insert into SecondPath
2 select distinct seq.nextval,startpt,endpt,name
3 from firstPath
4 where firstpath.tag=0;
select distinct seq.nextval,startpt,endpt,name
*
ERROR 位于第 2 行:
ORA-02287: 此处不允许序号
select distinct seq.nextval,startpt,endpt,name
from
(select distinct * from firstPath
where firstpath.tag=0);
SQL> insert into SecondPath select distinct seq.nextval,startpt,endpt,name
2 from (select distinct * from firstPath where firstpath.tag=0);
insert into SecondPath select distinct seq.nextval,startpt,endpt,name
*
ERROR 位于第 1 行:
ORA-02287: 此处不允许序号
select distinct seq.nextval,a.startpt,a.endpt,a.name
from
(select distinct * from firstPath
where firstpath.tag=0) a;
下面可以啦:
insert into SecondPath
select seq.nextval,a.startpt,a.endpt,a.name
from
(select distinct * from firstPath
where firstpath.tag=0) a;
insert into SecondPath
select seq.nextval,a.startpt,a.endpt,a.name
from
(select distinct * from firstPath
where firstpath.tag=0) a;
select seq.nextval,startpt,endpt,name
from firstpath
where rowid in
(select min(rowid) from firstpath
where firstpath.tag=0
group by startpt, endpt)这样就行了!