一、sql指定排序规则
1. oracle
select l.*
from learner l
where l.study_num in ('1760010025', '1660030817', '1860039508', '1660056123')
order by instr('1760010025,1660030817,1860039508,1660056123', l.study_num)
需要注意的是instr(参数1,参数2)方法,“参数1”为一个单号内逗号分隔的字符串;“参数2”为指定排序的字段。
2. mysql
select l.*
from learner l
where l.study_num in ('1760010025', '1660030817', '1860039508', '1660056123')
order by FIELD(l.study_num, '1760010025', '1660030817', '1860039508', '1660056123')
二、 筛选id最大记录
当两条数据重复需要删除id最大或最小的一条记录时候,前提是id为自增长的数字
SELECT
i.*
FROM
t_bas_learner l,
t_fee_invoice i
WHERE
l.LEARNER_ID = i.LEARNER_ID
AND i.INVOICE_ID = (
SELECT
max(i2.INVOICE_ID)
FROM
t_fee_invoice i2
WHERE
l.LEARNER_ID = i2.LEARNER_ID
)
AND l.STUDY_NUM IN (
'18114120042',
'18114120001'
)
这里记录的是批量删除id最大记录。如果id最小max替换为min
三、查询插入数据
mysql 相对简单,一般mysql中的id都为自动生成,不用关心。
insert into table2(testname,createtime,falg)
select t1.testname,t1.createtime,t1.falg from table1 t1;
oracle表中自增长的id是维护在sequences中,名称规则一般为SEQ_表名
insert into table2(id,testname,createtime,falg)
select seq_table2.nextval, t1.testname,t1.createtime,t1.falg from table1 t1;
seq_table2.nextval 为查询table2表的自增长id