SQL篇
常用sql
余大冠子
我在人间捡砖头,一砖一砖搬代码
展开
-
oracle根据汇总报表计算结余
汇总报表格式:计算结余,如下图格式:oracle语句:select max(id)id,max(create_date)jqdate,sum(numbers)jskc,productname,changs from uf_model_ctzhz group by changs,productname展示视图效果:原创 2022-01-19 10:38:29 · 1154 阅读 · 0 评论 -
oracle查询近三天数据
select * from '表名' where to_date('表单日期名','yy-mm-dd')>=trunc(sysdate -2)想要查询近几天就改最后一个数字。原创 2021-12-16 19:25:29 · 5490 阅读 · 0 评论 -
oracle查询某字段为空时,赋值为0
场景:当查询数据库的值为空时,这个值又需要后续的计算,这个时候就得把空值赋值为0例子:select SUM(A.YQNJ) AS YQNJ from formtable_main_40 A where A.qjqsrq>=CONCAT(to_char(sysdate,'yyyy'),'-03-01')方法1:用oracle自带的函数NVL(字段,值);select NVL(SUM(A.YQNJ),0) AS YQNJ from formtable_main_40 A .原创 2021-11-18 09:48:47 · 19531 阅读 · 0 评论 -
oracle查询null的空字段
oracle查询为空的数据为空的属于特殊值,这样的语句查不出select gysname,gysdl,gysxl from formtable_main_674 where gysdl=1 and gysxl=''这情况我们用is来查询select gysname,gysdl,gysxl from formtable_main_674 where gysdl=1 and gysxl is null...原创 2021-11-08 14:57:41 · 2334 阅读 · 0 评论 -
OA数据库宕机处理
登陆到数据库服务器windows:直接进入cmd之下下边命令linux:需要切换到oracle账号执行下边命令rman target /RMAN> crosscheck archivelog all; RMAN> delete expired archivelog all; RMAN>delete archivelog until time 'sysdate-3';...原创 2021-09-27 14:45:02 · 549 阅读 · 0 评论 -
oracle查询某表近两个月数据
select * from uf_kqsjcx where to_char(to_date(Diary_date,'yyyy-mm-dd'),'yyyy-mm') >= to_char(add_months(sysdate,-1),'yyyy-mm') and to_char(to_date(Diary_date,'yyyy-mm-dd'),'yyyy-mm')<= to_char(sysdate,'yyyy-mm')原创 2021-09-24 17:26:41 · 2258 阅读 · 0 评论 -
sqlserver查询某表近两个月数据
select * from V_OA_WorkTime_Query where Diary_date BETWEEN CONVERT(varchar(100),dateadd(dd,day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),23) and CONVERT(varchar(100),getdate(),23) order by Diary_date desc原创 2021-09-24 17:10:05 · 2101 阅读 · 0 评论 -
E8查询常用系统表名
流程表单jsp数据库绑定,id为流程idselect id,workflowname,custompage,custompage4Emoble from workflow_base where id=update workflow_base set custompage='/workflowcode/workflow/workflow_xxx.jsp' where id =绑定建模的jspselect id,modename,custompage from modeinfo wher原创 2021-09-15 15:40:38 · 380 阅读 · 0 评论 -
oracle日期详解(转载收藏)
1. 日期和字符转换函数用法(to_date,to_char)select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串 select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 .转载 2021-09-07 18:34:45 · 3732 阅读 · 0 评论