- 常用的排序为select * from tablename order by date , 这个date为字段,可以使用在数据表tablename中的字段顺序号代替,select * from tablename order by 4, (date字段为tablename中的第4 个字段)
- 有一个数据表,表名为TABLE,如果在应用中需要建立此表的一个临时表TABLE_TMP,要求和TABLE的结构全部一样,在ORACLE中可以使用
CREATE TABLE TABLE_TMP AS SELECT * FROM TABLE
但是这样的话TABLE中的数据记录也跟着导入了TABLE_TMP中,如果只要结构的话,使用
CREATE TABLE TABLE_TMP AS SELECT * FROM TABLE where 1!=1
注意,这张TABLE_TMP表结构和TABLE完全一样,但是TABLE_TMP不含有TABLE的index(索引) - 一段不常用的SQL语句 (把所有不含有付息账号的贷款分户改成不结息)
update lnrecd set paid_int_mtd = 3 where (bank_cd,accno_type,sbank_cd,gl_num,num,check1,loan_no) in
(select a.bank_cd,a.accno_type,a.sbank_cd,a.gl_num,a.num,a.check1,a.loan_no
from bank.lnrecd a,bank.lnacct b where a.paid_int_mtd!=3 and a.print_cnt!=2 and b.lint_bank_cd=0 and a.loan_status!=1 and a.loan_status!=3 and a.loan_status!=7 and a.bank_cd=b.bank_cd and a.accno_type=b.accno_type
and a.sbank_cd=b.sbank_cd and a.curr=b.curr and a.gl_num=b.gl_num and a.num=b.num and a.check1=b.check1) - 建立和使用oracle中的SEQUENCE
drop sequence abc_seq 删除名字为abc_seq的SEQUENCE
create sequence abc_seq increment by 1 start with 1 nocycle
cache 20 noorder 建立从1开始每次增加1的一个SEQUENCE
在使用insert语句时候可以使用此SEQUENCE,比如
insert into table name values(abc_seq.nextval,"姓名A")
使SEQUENCE的值增加
select abc_seq.nextval from dual abc_seq的SEQUENCE值加1
select abc_seq.nextval from table abc_seq的SEQUENCE值加table的条数 - 选择各网点当日总发生额不为0
select bank_cd,sbank_cd,sum(tran_amt) from bank.cutrjn group by
bank_cd,sbank_cd having sum(tran_amt)!=0 order by
bank_cd,sbank_cd
having必须放在order by前面