有用的SQL

最新推荐文章于 2024-09-09 20:25:50 发布

liuhuijunjsp

最新推荐文章于 2024-09-09 20:25:50 发布

阅读量73

点赞数

分类专栏： ORACLE数据库文章标签：数据库

本文链接：https://blog.csdn.net/liuhuijunjsp/article/details/84298435

版权

ORACLE数据库专栏收录该内容

31 篇文章 0 订阅

订阅专栏

--建序列
DROP SEQUENCE CMCS3_PROCESS_INSTANCE_INFO;

create sequence TransferBill_SEQ
minvalue 9999999999999999
maxvalue 999999999999999999999999999
start with 9999999999999999
increment by 1
cache 20;

create sequence TransferBillList_SEQ
minvalue 9999999999999999
maxvalue 999999999999999999999999999
start with 9999999999999999
increment by 1
cache 20;

--where ,group by ,having ,order by使用顺序
Select/update/delete…… from …… where …… group by …… having …… order by …… asc/desc

--删除和增加一列
alter table CMCS3_TRANSFER_BILL_LIST drop column PAYMENT_LIST_DATE

alter table CMCS3_TRANSFER_BILL_LIST add column PAYMENT_LIST_DATE
TIMESTAMP

--DB2查询序列方式
select NEXTVAL for MENU_SEQ from sysibm.sysdummy1;

--去掉char转化后的左边补充的00000000
SELECT TRIM(L ’0’ FROM '00123.45') FROM SYSIBM.SYSDUMMY1
将其他类型转换成char型直接用char()

--DB2的 DATE 和 TIMESTAMP 的区别
DATE 只能记录日期年月日
TIMESTAMP 能记录年月日时分秒

--外连接的问题
select count(*) from cmcs3_bank_account a left join cmcs3_transfer_bill b
on a.accountid = b.accountid
left join cmcs3_transfer_bill_list l
on b.transferbillid = l.transferbillid
where a.accountno = '1502206029300372918' and l.payeeaccountno = '138029283000004733' and l.payamount = '286900';

--oracle和DB2转换double型
to_number(tbl.payAmount) oracle
double(tbl.payAmount) DB2

--获取前20位数据库数据
FETCH FIRST 20 ROWS ONLY

--月末额度视图
DROP VIEW "CMBBCD_AH"."CMCS3_BANK_ACCOUNT_BALANCE_VIEW";

CREATE VIEW "CMBBCD_AH"."CMCS3_BANK_ACCOUNT_BALANCE_VIEW"
("ACCOUNTBALANCEID_", "ACCOUNTID_", "YEAR_", "MONTH_", "BALANCE_", "USEABLEBALANCE_",
"OVERDRAFT_" )
AS
select b.ACCOUNTBALANCEID as ACCOUNTBALANCEID_, b.ACCOUNTID as ACCOUNTID_,year(b.BALANCEDATE) as YEAR_,month(b.BALANCEDATE) as MONTH_, b.BALANCE as BALANCE_,b.USEABLEBALANCE as USEABLEBALANCE_,b.OVERDRAFT as OVERDRAFT_
from CMCS3_BANK_ACCOUNT_BALANCE b, (
select b1.ACCOUNTID as ACCOUNTID, max(b1.BALANCEDATE) as BALANCEDATE
from CMCS3_BANK_ACCOUNT_BALANCE b1 group by b1.ACCOUNTID,year(b1.BALANCEDATE),month(b1.BALANCEDATE) ) as b2
where b.ACCOUNTID = b2.ACCOUNTID and b.BALANCEDATE = b2.BALANCEDATE
WITH NO ROW MOVEMENT;