1. 截取ST20011_00000071字符串，从开始位置到‘—’的字符串
SELECT substr(ACC.STANDARD_ID,0,instr(ACC.STANDARD_ID,'_')-1)
FROM
CG_JS_SETTLE_ACCOUNTS_01 ACC
WHERE ACC.BALANCE_ID='JS0328000504'
总结：
Substr(a1,L1,L2) 把字符串a1从L1开始截取，截取长度为L2
如：substr(‘aldnajk’,0,5) ‘aldna’
**instr(a1,a2) ** 获取字符串a2在a1字符串中的位置
instr('sdhajdhj','a') 3
2. 多张表进行关联的语句，多张表与一张表有关联
select
contractItem.APPLY_DEPT_NAME,
contractItem.PROJECT_NAME,
(select item.TEC_VALUE_9 from CG_ITEM_T item where item.item_id=contractItem.item_id ) as LOGICAL_STARD,
ci1.quality_standard
from CG_CONTRACT_INFO_02 contractItem
left join CG_CONTRACT_INFO_01 ci1 on contractItem.BPO_ID = ci1.BPO_ID
and contractItem.VERSION_NO = ci1.VERSION_NO
where contractItem.bpo_id='YHT20201230735'
3. sql出现字符串丢失
SELECT
content_From,
content_To,
content_From||'大于'||content_To
FROM CG_JS_COMPONENT_VALUATIONTEMPLATE_02 S
WHERE STANDARD_ID in
(SELECT substr(ACC.STANDARD_ID, 0, instr(ACC.STANDARD_ID, '_') - 1)
FROM CG_JS_SETTLE_ACCOUNTS_01 ACC
WHERE ACC.BALANCE_ID = 'JS0328000506')
AND ELM_NAME = 'St.ad'
and WEIGHTED_AVERAGE_TYPE='c'
order by CONTENT_FROM asc
SELECT
content_From,
content_To,
to_char(content_From,'fm9999999999999990.00')||'大于'||to_char(content_To,'fm9999999999999990.00')
FROM CG_JS_COMPONENT_VALUATIONTEMPLATE_02 S
WHERE STANDARD_ID in
(SELECT substr(ACC.STANDARD_ID, 0, instr(ACC.STANDARD_ID, '_') - 1)
FROM CG_JS_SETTLE_ACCOUNTS_01 ACC
WHERE ACC.BALANCE_ID = 'JS0328000506')
AND ELM_NAME = 'St.ad'
and WEIGHTED_AVERAGE_TYPE='c'
order by CONTENT_FROM asc
4. 数字转化为日期格式
**1.数字转化为日期格式 20200203 —2020/02/03 **
select to_date(20150302,'yyyymmdd') from dual;
**2.字符串转化为 2020/02/03 --2020年02月03日 **
select to_char(to_date(contractItem.REQ_DELIVERY_DATE,'yyyy/mm/dd' ),'yyyy"年"mm"月"dd"日"')
from dual
**3.字符串转化为 2020/02转化为数字 202002的数字 **
select to_number(to_char(to_date('2021/04','yyyy/mm'),'yyyymm'))
from dual;
5.数据库查询出来的数据保留两位小数
select base_price from dual
select decode(BASE_PRICE,0,'0.00',trim(to_char(BASE_PRICE,'99999999999999.99'))) as base_Price_Two from dual
6.数据库查询出来的数据保留两位小数
select
(case when cdi.DEVICE_TAG is not null then cdi.DEVICE_TAG else '暂无数据'
from dual
7.多张表的部分字段合并成为一张表
效果展示：说明Aad和stad等都是同一张表中，但是所在的行不一样，怎么合并在同一行中，然后在润乾报表中同时扩展
with a as
(select rownum as n1, --结算单
fial1.rule_exp as rule_exp_q, --表达式范围
fial1.q_quality, --q质量
fial1.q_amount --q数量
from v_js_quality_bottom_FINALLY fial1
where fial1.ELM_ID in ('1103', '1002', '1003', '1004', '1005')
and fial1. BALANCE_ID = 'JS1009000025'
and fial1.ELM_ID = '1103'),
b as
(select rownum as n2, --结算单
fial2.rule_exp as rule_exp_AAD, --表达式范围
fial2.AAD_quality, --AAD质量
fial2.AAD_amount --AAD数量
from v_js_quality_bottom_FINALLY fial2
where fial2.ELM_ID in ('1103', '1002', '1003', '1004', '1005')
and fial2. BALANCE_ID = 'JS1009000025'
and fial2.ELM_ID = '1002'),
c as
(select rownum as n3, --结算单
fial1.rule_exp as rule_exp_stad, --表达式范围
fial1.stad_quality, --q质量
fial1.stad_amount --q数量
from v_js_quality_bottom_FINALLY fial1
where fial1.ELM_ID in ('1103', '1002', '1003', '1004', '1005')
and fial1. BALANCE_ID = 'JS1009000025'
and fial1.ELM_ID = '1003'),
d as
(select rownum as n4, --结算单
fial2.rule_exp as rule_exp_VAD, --表达式范围
fial2.VAD_quality, --AAD质量
fial2.VAD_amount --AAD数量
from v_js_quality_bottom_FINALLY fial2
where fial2.ELM_ID in ('1103', '1002', '1003', '1004', '1005')
and fial2. BALANCE_ID = 'JS1009000025'
and fial2.ELM_ID = '1004'),
e as
(select rownum as n5, --结算单
fial2.rule_exp as rule_exp_G, --表达式范围
fial2.G_quality, --AAD质量
fial2.G_amount --AAD数量
from v_js_quality_bottom_FINALLY fial2
where fial2.ELM_ID in ('1103', '1002', '1003', '1004', '1005')
and fial2. BALANCE_ID = 'JS1009000025'
and fial2.ELM_ID = '1005')
select RULE_EXP_AAD,
AAD_QUALITY,
AAD_AMOUNT,
RULE_EXP_STAD,
STAD_QUALITY,
STAD_AMOUNT,
RULE_EXP_VAD,
VAD_QUALITY,
VAD_AMOUNT,
RULE_EXP_Q,
Q_QUALITY,
Q_AMOUNT,
RULE_EXP_G,
G_QUALITY,
G_AMOUNT
from a
full join b
on a.n1 = b.n2
full join c
on c.n3 = b.n2
full join d
on d.n4 = c.n3
full join e
on e.n5 = a.n1
8.获取上月的最后一天
select to_char(last_day(add_months((select to_date('202104','yyyymm') from dual) , -1)), 'yyyy-mm-dd') as v_last_month
from dual;
9.oracle存储过程的写法
create or replace function check_settle_rule_value(BALANCE_ID_NEW in varchar2,
elm_name_NEW in varchar2,
elm_value_NEW in number)
return varchar2 is
formulaCode varchar2(50);
keyCount int;
begin
select count(*) into keyCount from V_JS_SETTLE_EXCLUDE_DEFAULT_PAYMENT s
where s.BALANCE_ID=BALANCE_ID_NEW and s.Elm_name=elm_name_NEW;
formulaCode:='';
for aa in(select LOGIC_FROM ,
LOGIC_TO,
CONTENT_FROM,
CONTENT_TO,
FORMULA_CODE
from V_JS_SETTLE_EXCLUDE_DEFAULT_PAYMENT s
where s.BALANCE_ID=BALANCE_ID_NEW and s.Elm_name=elm_name_NEW) loop
if aa.LOGIC_FROM='GTE' and aa.LOGIC_TO='LT' THEN
if aa.CONTENT_FROM<=elm_value_NEW and elm_value_NEW<aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GT' and aa.LOGIC_TO='LT' THEN
if aa.CONTENT_FROM<elm_value_NEW and elm_value_NEW<aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GT' and aa.LOGIC_TO='LTE' THEN
if aa.CONTENT_FROM<elm_value_NEW and elm_value_NEW<=aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GTE' and aa.LOGIC_TO='LTE' THEN
if aa.CONTENT_FROM<=elm_value_NEW and elm_value_NEW<=aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GT' and aa.LOGIC_TO='LTINFINITE' THEN
if aa.CONTENT_FROM>elm_value_NEW THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GTE' and aa.LOGIC_TO='LTINFINITE' THEN
if aa.CONTENT_FROM>=elm_value_NEW THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GTINFINITESIMAL' and aa.LOGIC_TO='LT' THEN
if elm_value_NEW<aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
if aa.LOGIC_FROM='GTINFINITESIMAL' and aa.LOGIC_TO='LTE' THEN
if elm_value_NEW<=aa.CONTENT_TO THEN
formulaCode:=aa.FORMULA_CODE;
end if;
end if;
keyCount:=keyCount-1;
exit when keyCount=0;
end loop;
return(formulaCode);
end check_settle_rule_value;
10.not in 语法的转换not exists，提高查询效率
not in
select * from tab1 where tab1.sid not in(select sid from tab2 where tab2.条件=tab1.条件)
not exists
select * from tab1 where not exists(select 1 from tab2 where tab2.条件=tab1.条件)
11.获取某个时间段里面年度数据
select
S.DW_ID, --入库单号
S.BATCH_ID, --批次号
S.MIN_POUND_DT_YMD, --计量时间起、
s.MAX_POUND_DT_YMD,--计量时间止
S.SUPPLIER_NAME, --供应商
S.ORIGIN_DESC, --产地
S.MAT_ACC_TYPE, --核算类型
S.MAT_ACC_TYPE_CH,--核算类型中文
S.ITEM_NAME, --物料名称
S.TRAN_COUNT, --车数
S.DAMP_WT, --入库湿量
S.MT_VALUE, --水分
S.Aad_VALUE, --灰分
S.Vad_VALUE, --挥发分
S.Stad_VALUE, --硫分
S.G_VALUE, --G值
S.SHIP_MODE, --运输方式
S.SHIP_MODE_CH,----运输方式中文
S.ORDER_ID, --订单号
S.BPO_ID, --合同号
S.JS_DAMP_WT,--结算量（湿量）
s.BLC_QTY ,--结算量（干量）
s.BLC_NOTAX_AMT,--货款不含税金额
s.BLC_AMT,--货款含税金额
s.BLC_NOTAX_PRICE--结算成本（不含税）
from V_CG_QUALITY_WEIGHTED S
WHERE 1=1
AND SUBSTR(S.MIN_POUND_DT_YMD,1,4) in(
SELECT distinct TO_CHAR(TO_DATE('20210701', 'yyyyMMdd') + ROWNUM - 1, 'yyyy') as daylist
FROM DUAL
CONNECT BY ROWNUM <=
trunc(to_date('20220423', 'yyyyMMdd') -
to_date('20210701', 'yyyy-MMdd')) + 1
)
AND SUBSTR(S.MAX_POUND_DT_YMD,1,4) in(
SELECT distinct TO_CHAR(TO_DATE('20210701', 'yyyyMMdd') + ROWNUM - 1, 'yyyy') as daylist
FROM DUAL
CONNECT BY ROWNUM <=
trunc(to_date('20220423', 'yyyyMMdd') -
to_date('20210701', 'yyyy-MMdd')) + 1
)
AND S.MAT_ACC_TYPE_CH='燃料煤'
AND S.ITEM_NAME='新疆喷吹煤'
AND S.SUPPLIER_NAME='水城锐鑫工贸有限公司'
12.把数据库某一列的数据合并为一列数据（LISTAGG）
select LISTAGG(remark, ‘,’) WITHIN GROUP(ORDER BY bill_no) as remark,
bill_no
from (select s.bill_no,
inv_no.remark
from cg_js_income_invoice s
left join cg_js_income_invoice_no inv_no on s.sid=inv_no.income_invoice_sid)
group by bill_no
13.树形结构的表格怎么使用SQL语句进行展示
页面显示
WITH TAB AS(
SELECT 0 AS SID,
0 AS PARENT_SID,
'ROOT_NODE' AS ROOT_NODE,
'基础主数据' AS TYPE_NAME,
'基础主数据' AS TYPE_DESC
FROM DUAL)
SELECT MENU_LEVEL,
MENU_NO,
PARENT_SID,
TYPE_ID,
TYPE_NAME,
TYPE_DESC,
TYPE_SEQ,
TYPE_LEVEL
FROM (
SELECT 1 AS MENU_LEVEL,
MUNU1.TYPE_SEQ*POWER(10,16) AS MENU_NO,
MUNU1.PARENT_SID,
MUNU1.TYPE_ID,
MUNU1.TYPE_NAME,
MUNU1.TYPE_DESC,
MUNU1.TYPE_SEQ,
MUNU1.TYPE_LEVEL
FROM SADP_MD_COMMON_TYPES MUNU1,TAB
WHERE MUNU1.PARENT_SID=TAB.SID
UNION ALL
SELECT 2 AS MENU_LEVEL,
MUNU1.TYPE_SEQ*POWER(10,16)+
MUNU2.TYPE_SEQ*POWER(10,15) AS MENU_NO,
MUNU2.PARENT_SID,
MUNU2.TYPE_ID,
MUNU2.TYPE_NAME,
MUNU2.TYPE_DESC,
MUNU2.TYPE_SEQ,
MUNU2.TYPE_LEVEL
FROM SADP_MD_COMMON_TYPES MUNU2,
SADP_MD_COMMON_TYPES MUNU1,
TAB
WHERE MUNU2.PARENT_SID=MUNU1.SID
AND MUNU1.PARENT_SID=TAB.SID
UNION ALL
SELECT 2 AS MENU_LEVEL,
MUNU1.TYPE_SEQ*POWER(10,16)+
MUNU2.TYPE_SEQ*POWER(10,15)+
MUNU3.TYPE_SEQ*POWER(10,14) AS MENU_NO,
MUNU3.PARENT_SID,
MUNU3.TYPE_ID,
MUNU3.TYPE_NAME,
MUNU3.TYPE_DESC,
MUNU3.TYPE_SEQ,
MUNU3.TYPE_LEVEL
FROM SADP_MD_COMMON_TYPES MUNU3,
SADP_MD_COMMON_TYPES MUNU2,
SADP_MD_COMMON_TYPES MUNU1,
TAB
WHERE MUNU3.PARENT_SID=MUNU2.SID
AND MUNU2.PARENT_SID=MUNU1.SID
AND MUNU1.PARENT_SID=TAB.SID
)W
ORDER BY W.MENU_NO
数据库显示
14.获取当前1号到当前日期的sql语句
SELECT TO_DATE('2023-02-01','yyyy-MM-dd')+rownum-1 as dateday
from dual
connect by rownum<=(TO_DATE('2023-02-22','yyyy-MM-dd')-to_date('2023-02-01','yyyy-MM-dd'))
15.获取当前日期上一个工作日
CREATE OR REPLACE FUNCTION GET_LAST_WORKDAY(p1 in varchar2)
return varchar2
is
LAST_WEEK_DATE varchar2(20);
IS_FLAG varchar2(10);
SUB_NUM number:=1;
begin
SELECT WEEKDATE into LAST_WEEK_DATE from V_XS_SP_WEEKDAY WHERE WEEKDATE=to_char(to_date(p1,'yyyy-mm-dd')-SUB_NUM,'yyyy-mm-dd');
SELECT IF_WEEKDAY into IS_FLAG from V_XS_SP_WEEKDAY WHERE WEEKDATE=to_char(to_date(p1,'yyyy-mm-dd')-SUB_NUM,'yyyy-mm-dd');
while IS_FLAG!=1 loop
SUB_NUM:=SUB_NUM+1;
SELECT WEEKDATE into LAST_WEEK_DATE from V_XS_SP_WEEKDAY WHERE WEEKDATE=to_char(to_date(p1,'yyyy-mm-dd')-SUB_NUM,'yyyy-mm-dd');
SELECT IF_WEEKDAY into IS_FLAG from V_XS_SP_WEEKDAY WHERE WEEKDATE=to_char(to_date(p1,'yyyy-mm-dd')-SUB_NUM,'yyyy-mm-dd');
end loop;
return LAST_WEEK_DATE;
end;
说明：表V_XS_SP_WEEKDAY 里面有两个字段，WEEKDATE 是当前日期，IF_WEEKDAY 获取当前日期是否是工作日。
16.创建oracle自定义表格函数
--创建自定义行
CREATE OR REPLACE TYPE TYPE_TTEMP_ROW AS OBJECT(ELE_ID VARCHAR2(10), ELM_VALUE NUMBER(20));
--创建自定义表格
CREATE OR REPLACE TYPE TYPE_TEMP_TABLE AS TABLE OF TYPE_TTEMP_ROW;
--创建对应的方法
CREATE OR REPLACE FUNCTION GET_OVERFLOW_DW_ID(BALANCE_ID in varchar2)
--该方法获取元素是否按照公式一口价计算，返回入按照公式一口价的入库单号
--参数说明：BALANCE_ID：结算单号
--created 20231127
--createBy:Nickel
RETURN TYPE_TEMP_TABLE IS
TEMP_ROW TYPE_TTEMP_ROW;--定义行
TEMP_TABLE TYPE_TEMP_TABLE:=TYPE_TEMP_TABLE();--定义返回结果，并初始化
BEGIN
FOR CURROW IN(SELECT ACC03.ELM_ID, ACC03.ELM_VALUE FROM CG_JS_SETTLE_ACCOUNTS_03 ACC03
WHERE ACC03.ADD_SUB_PRICE = '1'AND ACC03.BALANCE_ID =BALANCE_ID)
LOOP
TEMP_ROW:= TYPE_TTEMP_ROW(CURROW.ELM_ID,CURROW.ELM_VALUE);--获取一行数据
TEMP_TABLE.EXTEND; --表类型增加一行
TEMP_TABLE(TEMP_TABLE.COUNT):= TEMP_ROW;--一行放进去
END LOOP;
RETURN(TEMP_TABLE);
END;
--允许自定义的表格
SELECT * FROM TABLE(GET_OVERFLOW_DW_ID('JS2311270030'))
410
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
