常用数据库公式记录

常用数据库公式

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

目录

常用数据库公式

前言

一、SqlSever

---

---

前言

本文记录报表开发过程中各个数据库语言常用公式，以便复用。 

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、SqlSever

1.常用SQL

--1.中文月份转化
select
case right(year_mon,2)*1 
when 1 then '一' 
when 2 then '二'
when 3 then '三'
when 4 then '四'
when 5 then '五'
when 6 then '六'
when 7 then '七'
when 8 then '八'
when 9 then '九'
when 10 then '十'
when 11 then '十一'
else '十二' end as assis_months
from table

--2.保留两位小数
select Convert(decimal(18,2),sum(plan_cost)) as plan_cost from table

--3.返回当前时间/年月
select CONVERT(varchar,GETDATE(),120) as '当前时间'--"2020-01-01 02:01:02"
select  convert(char(7),getdate(),120)---2020-09

--4.返回当月/上月/去年
CONVERT(varchar(100),DATEADD(MONTH,0,concat(year_mon,'-01')), 23) as this_mon
CONVERT(varchar(100),DATEADD(MONTH,-1,concat(year_mon,'-01')), 23) as last_mon
CONVERT(varchar(100),DATEADD(MONTH,-12,concat(year_mon,'-01')), 23) as last_year

--5.计算同比、环比需要使用到表格自关联时时间的简便写法,可避免复杂的时间转化
tableA.year*12+month*1  =   tableB.year*12+month*1-12

2.存储过程

--1.含参数的存储过程
--本段不包含参数为空则选择全部的写法

DROP PROCEDURE PRO_COST_FOLLOW;--删除存储过程

execute PRO_COST_FOLLOW '2020','01'--执行含参数的存储过程



create  procedure PRO_COST_FOLLOW
@yyear char(4),
--@mon char(2),
@pro_id char(128)

AS
BEGIN

--SET NOCOUNT ON

delete from 
FILL_PRO_COST_DETAILS
where left(year_mon,4)=@yyear
and fa_acccode='ACC200000'
--and (right(year_mon,2)=@mon)
and (pro_id=@pro_id);

insert into FILL_PRO
(year_mon,pro_id,fa_acccode,chil_accname,plan_cost,actu_cost,update_date,update_user,UPDATE_DATE_PROC)

select 
FILL_FINANCE_ACCOUNT_ACT.dyear+'-'+FILL_FINANCE_ACCOUNT_ACT.dmonth as year_mon,
FILL_FINANCE_ACCOUNT_ACT.pro_name as pro_id,
'ACC200000' as fa_acccode,
acc_name as chil_accname,
FILL_FINANCE_ACCOUNT_BDG.back_budget/12 as plan_cost,
back_actual as actual_cost,
GETDATE() as update_date,
'procedure' as update_user,
getdate() as UPDATE_DATE_PROC
from  FILL_FINANCE_TABLE1
left join FILL_FINANCE_ACCOUNT on FILL_FINANCE_ACCOUNT_ACT.acc_id=FILL_FINANCE_ACCOUNT.acc_id
left join FILL_FINANCE_ACCOUNT_BDG on FILL_FINANCE_ACCOUNT_BDG.acc_id=FILL_FINANCE_ACCOUNT_ACT.ACC_ID and FILL_FINANCE_ACCOUNT_BDG.dyear=FILL_FINANCE_ACCOUNT_ACT.dyear
and FILL_FINANCE_ACCOUNT_BDG.pro_name=FILL_FINANCE_ACCOUNT_ACT.pro_name
and FILL_FINANCE_ACCOUNT_ACT.dyear=FILL_FINANCE_ACCOUNT_BDG.dyear
where FILL_FINANCE_ACCOUNT.parent_id='23b2a06b94f944e493dd2daa4bc74293'
and FILL_FINANCE_ACCOUNT_ACT.dyear=@yyear
--and (FILL_FINANCE_ACCOUNT_ACT.dmonth=@mon)
and FILL_FINANCE_ACCOUNT_ACT.pro_name=@pro_id;
select 1 ;
END

 

 

---