1.sql中返回序号
select rownum , * from tablename where 条件语句
select row_number() over (order by 字段 ) as xxx, * from tablename where 条件语句
2.删除数据库中重复的字段
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people where
peopleId in
(select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in
(select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a where
(a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a where
(a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in
(select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a where
(a.peopleId,a.seq) in
(select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in
(select min(rowid) from vitae group by peopleId,seq having count(*)>1)
3.新增字段和备注
Alter Table building Add OUTLINE_ID VARCHAR2(32)
comment on column buildings.OUTLINE_ID is '轮廓ID ';
//修改字段名
alter table 表名 rename column 字段名 to 新字段名
4.批量进行插入(可以拼接多张表的信息插入)
INSERT INTO XX(表名)(id,name,code,type,time)
SELECTid,name,code,type,sysdate time
FROM (select
sys_guid() id,
name,
from xxx
5.orcal字符串排序问题比如A1A-A11这样的排序会乱掉使用ORcal的正则函数进行排序
order by to_number(regexp_substr(t.name,'[0-9]*[0-9]',1))