oracle 多个字符替换实现

名站网址导航为大家提供关于数据库教程相关的教程网站知识。

深入Oracle的left join中on和where的区别详解
今天遇到一个求某月所有天数的统计结果，假如如果某日的结果是0也需要显示出来，即: 日期交易次数交易金额 2009-4-01 1 10 2009-4-02 2 20 2009-4-03 0 0 2009,一开始我用的左连接，用on做为两表关联条件，用where作为过滤条件，但是发现0的数据根本不显示，后来把where关键字去掉，把过滤条件都放到on里，问题解决，网上一搜，找到了答案：网站数据库在通过连接,两条SQL: 1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA' 2、select,第二条SQL的过程： 1、中间表 on条件: tab1.size = tab2.size and tab2.name='AAA' (条件不为真也会返回左表中的记录) tab1.id tab1.size

复制具体相关代码具体相关代码如下:

create table A_TEST
(
PAYOUT_ITEM_CODE VARChAR2(30) not null,
FORMULA_DET VARChAR2(1000)
)

create table B_TEST
(
ELEMENT_ID VARChAR2(5) not null,
NAME VARChAR2(41)
)

FORMULA_DET列里ELEMENT_ID替换成NAME
测试数据如下

复制具体相关代码具体相关代码如下:

insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30228', '({30015} {30016})*450');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*1500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*5000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*2500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*2300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*1150');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30104', '({30015} {30016})*300*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*2300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*5000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*3000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30102', '({30015} {30016})*1500');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30006} {30061} {30008}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*3800*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({30030} {30031} {30032})*38000 {30033}*23000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30210', '({30030} {30031} {30032} {30033})*38000 {30036}*10000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30229', '({30015} {30016})*1400');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30228', '({30015} {30016})*450');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30216', '({30015} {30016})*1300');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30216', '({30015} {30016})*650');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30307', '({30015} {30016})*360');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30051}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30052}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30053}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30054}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30055}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30302', '{30056}');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*4000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*3800');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*100*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*500*12');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30060}*0');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}/{30057}*150000');
insert into a_test (PAYOUT_ITEM_CODE, FORMULA_DET)
values ('30226', '{30057}*6000');

复制具体相关代码具体相关代码如下:

insert into b_test (ELEMENT_ID, NAME)
values ('30006', 'a1');
insert into b_test (ELEMENT_ID, NAME)
values ('30008', 'a2');
insert into b_test (ELEMENT_ID, NAME)
values ('30009', 'a3');
insert into b_test (ELEMENT_ID, NAME)
values ('30010', 'a4');
insert into b_test (ELEMENT_ID, NAME)
values ('30015', 'a5');
insert into b_test (ELEMENT_ID, NAME)
values ('30016', 'a6');
insert into b_test (ELEMENT_ID, NAME)
values ('30017', 'a7');
insert into b_test (ELEMENT_ID, NAME)
values ('30018', 'a8');
insert into b_test (ELEMENT_ID, NAME)
values ('30019', 'a9');
insert into b_test (ELEMENT_ID, NAME)
values ('30020', 'a10');
insert into b_test (ELEMENT_ID, NAME)
values ('30021', 'a11');
insert into b_test (ELEMENT_ID, NAME)
values ('30022', 'a12');
insert into b_test (ELEMENT_ID, NAME)
values ('30023', 'a13');
insert into b_test (ELEMENT_ID, NAME)
values ('30024', 'a14');
insert into b_test (ELEMENT_ID, NAME)
values ('30025', 'a15');
insert into b_test (ELEMENT_ID, NAME)
values ('30026', 'a16');
insert into b_test (ELEMENT_ID, NAME)
values ('30027', 'a17');
insert into b_test (ELEMENT_ID, NAME)
values ('30028', 'a18');
insert into b_test (ELEMENT_ID, NAME)
values ('30029', 'a19');
insert into b_test (ELEMENT_ID, NAME)
values ('30030', 'a20');
insert into b_test (ELEMENT_ID, NAME)
values ('30031', 'a21');
insert into b_test (ELEMENT_ID, NAME)
values ('30032', 'a22');
insert into b_test (ELEMENT_ID, NAME)
values ('30033', 'a23');
insert into b_test (ELEMENT_ID, NAME)
values ('30034', 'a24');
insert into b_test (ELEMENT_ID, NAME)
values ('30035', 'a25');
insert into b_test (ELEMENT_ID, NAME)
values ('30036', 'a26');
insert into b_test (ELEMENT_ID, NAME)
values ('30037', 'a27');
insert into b_test (ELEMENT_ID, NAME)
values ('30038', 'a28');
insert into b_test (ELEMENT_ID, NAME)
values ('30039', 'a29');
insert into b_test (ELEMENT_ID, NAME)
values ('30040', 'a30');
insert into b_test (ELEMENT_ID, NAME)
values ('30041', 'a31');
insert into b_test (ELEMENT_ID, NAME)
values ('30042', 'a32');
insert into b_test (ELEMENT_ID, NAME)
values ('30043', 'a33');
insert into b_test (ELEMENT_ID, NAME)
values ('30044', 'a34');
insert into b_test (ELEMENT_ID, NAME)
values ('30045', 'a35');
insert into b_test (ELEMENT_ID, NAME)
values ('30046', 'a36');
insert into b_test (ELEMENT_ID, NAME)
values ('30047', 'a37');
insert into b_test (ELEMENT_ID, NAME)
values ('30048', 'a38');
insert into b_test (ELEMENT_ID, NAME)
values ('30049', 'a39');
insert into b_test (ELEMENT_ID, NAME)
values ('30050', 'a40');
insert into b_test (ELEMENT_ID, NAME)
values ('30051', 'a41');
insert into b_test (ELEMENT_ID, NAME)
values ('30052', 'a42');
insert into b_test (ELEMENT_ID, NAME)
values ('30053', 'a43');
insert into b_test (ELEMENT_ID, NAME)
values ('30054', 'a44');
insert into b_test (ELEMENT_ID, NAME)
values ('30055', 'a45');
insert into b_test (ELEMENT_ID, NAME)
values ('30056', 'a46');
insert into b_test (ELEMENT_ID, NAME)
values ('30057', 'a47');
insert into b_test (ELEMENT_ID, NAME)
values ('30058', 'a48');
insert into b_test (ELEMENT_ID, NAME)
values ('30059', 'a49');
insert into b_test (ELEMENT_ID, NAME)
values ('30060', 'a50');
insert into b_test (ELEMENT_ID, NAME)
values ('30061', 'a51');

这个假如如果用function或者是sp做，就没有什么难度了。
但是用sql做就比较难度了

复制具体相关代码具体相关代码如下:

关于数据库教程相关的教程网站知识今天我们就说到这里了，希望可以帮到大家。

oracle 多个字符替换实现

宝贝市场2023-02-08

夺宝助手2023-02-08

查诚信2023-02-08

车价天天报2023-02-08

考勤助理小程序2023-02-08

汽车报价大全查询2023-02-08