目录
一、基础查询
这个还是相对简单的,只是格式比较“花里胡哨”
源码:
法人客户贷款资金交易流水:
%LET data_dt='20190601';
%LET data_dt2='20190630';
proc sql;
create table test1 as
select *
from connection to postgres
(
SELECT
DISTINCT(t1.Event_Id) AS "事件编号"
,t1.Party_Id AS "客户代码"
,t2.Cntpty_Acct_Name AS "借款人名称"
,t1.Agt_Num AS "结算账号"
,t1.Work_Dt AS "操作日期"
,t1.Event_Dt AS "业务发生日"
,(t1.Event_Dt-t2.Event_Dt) AS "天数"
,t1.Event_Tm AS "交易时间"
,t1.Seq_Num AS "顺序号"
,t1.Curr_Cd AS "币种"
,t1.Tx_Cd AS "交易代码"
,t1.Tx_Amt/10000 AS "业务发生额"
,t1.Debit_Crdt_Cd AS "借贷方"
,t1.Bal/10000 AS "余额"
,t1.Cntpty_Acct_Name AS "对方名称"
,t1.Cntpty_Party_Id AS "对方代码"
,t1.Cntpty_Agt_Num AS "对方账号"
,t1.Usage AS "用途"
,t1.Postsc AS "摘要"
,t1.Memo AS "备注"
FROM
dwpview.T05_CORP_CURR_DPSIT_ACCT_EVENT AS t1
INNER JOIN
(select Party_Id,Cntpty_Acct_Name,Agt_Num,Event_Dt,Tx_Amt
from dwpview.T05_CORP_CURR_DPSIT_ACCT_EVENT
where (Tx_Cd='02201' or Tx_Cd='03728')
AND Debit_Crdt_Cd='2'
and Party_Id=Cntpty_Party_Id
and Event_Dt>=&data_dt.
and Event_Dt<=&data_dt2
and Tx_Amt>=10000) AS t2
ON t1.Party_Id=t2.Party_Id
WHERE
t1.Event_Dt=t2.Event_Dt
AND t1.Cntpty_Acct_Name not like '%利息%'
AND t1.Cntpty_Acct_Name not like '%其它%'
AND t1.Cntpty_Acct_Name not like '%其他%'
and t1.Cntpty_Acct_Name not like '%待报解%'
and t1.Cntpty_Acct_Name not like '%待清算%'
and t1.Memo not like '%协定存款汇划款项%'
and t1.Party_Id<>'120290000950013'
AND t1.Event_Dt>=&data_dt.
AND t1.Event_Dt-t2.Event_Dt<30
AND t1.Tx_Amt>=10000
);
disconnect from postgres;
quit;
1、查询的基本格式
先来从简单的一步一步来看:
格式:
SELECT
字段1
,字段2
,...
FROM
表名
这是最简单的一个查询基本格式,就是从 指定的表中查询到我们想要的字段
所有的查询都是建立在这个的基础上变得复杂起来的
实例
现在有一张emp表,其中的字段以及数据分别是:
需求:查询emp表中的姓名以及性别字段
分析:查询的表名是emp表,查询的字段分别是姓名(name)和性别(gender)
SELECT
name
,gender
FROM
emp
查询结果
注:这里的写法是我的个人习惯,所有的关键字(例如SELECT,FROM等)喜欢用大写(其实大写小写都是一样的,甚至也可以一个字母大写一个字母小写这么来,只是这么写不美观)。另一个习惯是把多个字段前的“,”写在下一个字段的前面。
2、查询表中的所有字段
有时候一个表中的字段特别多,但是我们需要查询这张表中的所有字段,就可以使用"*"通配符快速获取
格式:
SELECT
*
FROM
表名
实例
需求:查询emp表中的所有字段信息
分析:查询的表是emp表,字段是所有的字段,选择使用“*”
SELECT
*
FROM
emp
查询结果:
注:当然也可以不使用“*”,而是通过把表中所有的字段都列出来进行查询
3、字段,表的别名
说明:
别名就可以直接理解为其起了一个绰号,可能由于这个字段名或者表名太长或者其他原因,起一个比较容易使人理解的名字方便记忆和使用。
AS 这个关键字是可以省略的。
语法:
/*给字段添加别名*/
字段名 AS 字段别名
/*给表添加别名*/
表名 AS 表别名
实例
需求:查询emp表的name字段和sal字段并且分别定义别名为“姓名”和“薪资”,并且给表名起别名为“员工表”
代码:
SELECT
name AS 姓名
,sal 薪资
FROM
emp 员工表
查询结果:
注:这里的name字段后面加了AS ,sal字段和emp表名后面没有添加AS ,但是同样可以成功添加别名
4、distinct()函数
说明:distinct函数用于在不分组的情况下对指定字段进行去除重复(简称:去重)操作,一般用于查看这个字段在表中的数据有多少种分组
语法:
distinct(字段名)
实例
现在有一张demo0001表,其中的数据为
需求:查看字段AC_DATE在demo0001表中有多少种数据
分析:
如果我们简单的对AC_DATE这个字段进行查询的话,得到的结果如下图:
会将这个表中AC_DATE所有的数据都输出,但通过图中的结果,我们能明显看出存在多个20211231和20210310的数据,此时要对其进行去重,于是得到:
查询结果:
这样,重复多余的相同数据就会被去掉,只留下这种数据的一条记录
5、表连接
说明:
表连接是数据库查询中很重要的一环,因为多数情况下我们不只是使用一张表数据获取数据,需要从不同的表中获取数据后再进行加工处理。这时候就需要表连接。主要使用的表连接有四种,分别是左连接、右连接、内连接和全连接。个人习惯一般把左右表也称作主副表。
表连接是需要需要通过某一个(相同数据)的字段进行关联的,称为两张表的关联关系
按照分类的话可以分为内连接和外连接,外连接又具体分为左(外)连接,右(外)连接,全(外)连接
语法:
/*左连接*/
主表名
LEFT JOIN 副表名
ON 关联条件
/*右连接*/
主表名
RIGHT JOIN 副表名
ON 关联条件
/*内连接*/
主表名
INNER JOIN 副表名
ON 关联条件
/*全连接*/
主表名
FALL JOIN 副表名
ON 关联条件
左连接
图解:
说明:
左连接使用的比较频繁,一般是以左表的字段的数据为主,关联右表时,右表有的字段会被填充,右表没有的字段为null,如果数据只在右表中存在,左表不存在时,数据是不会显示的。
是以左表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出,而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接,是外连接的一种
语法:
SELECT
字段1
,字段2
,...
FROM
左表
LEFT JOIN
右表
ON 关联条件
实例
demo0001和emp表的数据分别为
需求:查询emp表中所有人在demo0001中的CUS_NO字段信息
分析:要查询emp表中的所有人,由于要使用左连接,那么以emp表为主表,左连接demo0001表,关联的条件是emp表中的id与demo0001表中的ID相同
代码:
SELECT
emp.id
,emp.name
,demo0001.CUS_NO
FROM
emp
LEFT JOIN
demo0001
ON emp.id=demo0001.ID
查询结果
解析:通过结果可以看出,把emp表的所有id和name的数据都查询了出来,但是对于demo0001表来说,只查询到了其中有数据的部分,没有数据的部分在结果中显示为null
右连接
图解:
说明:
右连接与左连接类似,只是最终的查询结果是右表中的全部数据,左表中比右表多出来的数据被抛弃,左表比右表少的数据显示为null
语法:
SELECT
字段1
,字段2
FROM
左表
RIGHT JOIN
右表
ON 关联条件
实例
demo0001中添加一行ID为0的数据
需求:查询demo0001中的CUS_NO在emp中的name信息
分析:查询的主体是demo0001中的CUS_NO 需要使用右连接,那么以demo0001作为右表,查询emp表中的name
代码:
SELECT
demo0001.ID
,demo0001.CUS_NO
,emp.name
FROM
emp
RIGHT JOIN
demo0001
ON emp.id=demo0001.ID
查询结果:
解析:查询结果中,查询到了demo0001中的所有ID数据,在emp表中有对应的会显示在后面的name字段中,而id为0的在emp表中不存在,所以没有显示为null。
内连接
图解:
说明:
内连接也是使用比较频繁的连接之一。将主表和副表其中公共部分的数据提取出来。
实例
需求:
以demo0001为主表,获取与emp表中公共部分的CUS_NO以及name信息
分析:
以demo0001作为主表inner join关联emp表,CUS_NO来自demo0001,name来自emp
代码:
SELECT
emp.name
,demo0001.CUS_NO
FROM
demo0001
INNER JOIN
emp
ON emp.id=demo0001.ID
查询结果:
全连接
图解:
说明:
返回两张表左右的部分。
注:MYSQL是不支持全连接的。
6、算数运算符和比较运算符
说明:
和其他的语言一致,也存在运算符。比较常见的如下:
/*加法*/
+
/*减法*/
-
/*乘法*/
*
/*除法*/
/
/*大于*/
>
/*小于*/
<
/*等于*/
=
/*不等于*/
!= 或 <>
/*大于等于*/
>=
/*小于等于*/
<=
实例
需求:
查询emp表中,sal大于等于3000的全部信息
代码:
SELECT
*
FROM
emp
WHERE
sal>=3000
查询结果:
解析:
这里使用到了WHERE条件语句,一般位置是在关联表之后,WHERE后面跟查询的条件,多个条件之间使用AND并列
7、模糊查询
说明:使用like或者not like,表示像或不像,后使用%代表模糊的位置
实例
需求:查询emp表中,姓刘,且性别不为男的全部信息
代码:
SELECT
*
FROM
emp
WHERE
name LIKE '刘%'
AND
gender <> '男'
查询结果:
解析:sql代码中字符串使用单引号''引用起来。由于查询姓刘,意味着name字段的第一个字是刘,后面部分为模糊查询,使用%代替。性别不为男,也可以使用 gender NOT LIKE '男' 来表示。
8、实战案例
现在有了前面的基础知识,再回过头来看开头的那段代码
%LET data_dt='20190601';
%LET data_dt2='20190630';
proc sql;
create table test1 as
select *
from connection to postgres
(
SELECT
DISTINCT(t1.Event_Id) AS "事件编号"
,t1.Party_Id AS "客户代码"
,t2.Cntpty_Acct_Name AS "借款人名称"
,t1.Agt_Num AS "结算账号"
,t1.Work_Dt AS "操作日期"
,t1.Event_Dt AS "业务发生日"
,(t1.Event_Dt-t2.Event_Dt) AS "天数"
,t1.Event_Tm AS "交易时间"
,t1.Seq_Num AS "顺序号"
,t1.Curr_Cd AS "币种"
,t1.Tx_Cd AS "交易代码"
,t1.Tx_Amt/10000 AS "业务发生额"
,t1.Debit_Crdt_Cd AS "借贷方"
,t1.Bal/10000 AS "余额"
,t1.Cntpty_Acct_Name AS "对方名称"
,t1.Cntpty_Party_Id AS "对方代码"
,t1.Cntpty_Agt_Num AS "对方账号"
,t1.Usage AS "用途"
,t1.Postsc AS "摘要"
,t1.Memo AS "备注"
FROM
dwpview.T05_CORP_CURR_DPSIT_ACCT_EVENT AS t1
INNER JOIN
(select Party_Id,Cntpty_Acct_Name,Agt_Num,Event_Dt,Tx_Amt
from dwpview.T05_CORP_CURR_DPSIT_ACCT_EVENT
where (Tx_Cd='02201' or Tx_Cd='03728')
AND Debit_Crdt_Cd='2'
and Party_Id=Cntpty_Party_Id
and Event_Dt>=&data_dt.
and Event_Dt<=&data_dt2
and Tx_Amt>=10000) AS t2
ON t1.Party_Id=t2.Party_Id
WHERE
t1.Event_Dt=t2.Event_Dt
AND t1.Cntpty_Acct_Name not like '%利息%'
AND t1.Cntpty_Acct_Name not like '%其它%'
AND t1.Cntpty_Acct_Name not like '%其他%'
and t1.Cntpty_Acct_Name not like '%待报解%'
and t1.Cntpty_Acct_Name not like '%待清算%'
and t1.Memo not like '%协定存款汇划款项%'
and t1.Party_Id<>'120290000950013'
AND t1.Event_Dt>=&data_dt.
AND t1.Event_Dt-t2.Event_Dt<30
AND t1.Tx_Amt>=10000
);
disconnect from postgres;
quit;
开头几行看不懂没关系,从第8行开始一直到52行,应该是大致看着没有问题的
1、首先从第9行开始一直到28行是从表中获取需要的字段,同时在后面使用AS分别起别名。第10行使用DISTENCT()函数,对Event_Id进行去重处理。第15行是两个数据做差,计算得出天数。第20行和第22行分别做了除法运算,除以10000,说明此字段最后的统计单位是万元。
2、从第29行开始一直到第40行是表的连接,主表为t1表,内连接了一个t2(第39行)表,只不过这个t2表比较长,使用了一个单独的查询来作为t2表。第40行是t1和t2的关联条件。
3、t2表解析:第32行是查询的字段,第33行是查询的表,从第34行到39行是查询的条件。最后使用AS,将这个查询整体命名为t2表。
4、从第41行到52行是整个表的查询条件,分别使用了模糊查询,比较运算符和算数运算符,并且多个条件之间使用AND并列。
5、至于其他的地方,比如第1行和第2行应该算是定义了两个参数,这两个参数用在了第37行和第38行,通过定义可变的参数,实现不同的结果。第3行应该是标识,没有特别的意义。第4行创建了一张表名为test1的表,把从第8行到第52行查询出来的整个表的数据插入其中。最后第54和55行也是标识,没有特别的意义。
总结
二、进阶教程
1、CURRENT_TIMSTAMP(0)
说明:获取当前时间和日期,在MYSQL中默认形式为YYYY-MM-DD HH:MM:SS
案例:
解析:可以看出,虽然在demo0001中并没有CURRENT_TIMESTAMP这个字段但是也可以查出来,而且数据显示结果为当前时间的年月日时分秒形式
2、ADD_MONTH(日期,int)
说明:
该方法有两个参数,第一个参数为日期,第二个参数是数字,这个数字正负数皆可,正数表示增加,含义为在当前日期的基础上增加若干个月,负数表示减少,含义为在当前日期的基础上减去若干个月。
注:MYSQL不支持ADD_MONTH()的方法
语法:
ADD_MONTH(日期,int)
实例:
ADD_MONTHS(date,int) 其中第一个参数为日期,第二个为按月增加的幅度
ADD_MONTHS(sysdate,+2) 表示当前日期两个月之后的时间
ADD_MONTHS(sysdate,-2) 表示当前日期的两个月之前的时间
3、TO_CHAR(日期,'日期格式')
说明:
该方法是用来将日期修改为指定格式的字符串形式,前一个参数为日期,后一个参数为日期格式的字符串,比如'YYYYMMDD'
注:MYSQL中不支持TO_CHAR()的方法
格式:
TO_CHAR(日期,'日期格式')
案例:
TO_CAHR(CURRENT_TIMESTAMP(0),'YYYYMMDD')
将当前日期改为只包含年月日的字符串
4、substr(字符串,int,int)
说明:该方法用来截取字符串,三个参数中,第一个参数是要被截取的字符串,第二个参数是开始截取的位置(sql中是从1开始),第三个参数是总共要截取的长度。其中第三个参数可以省略,表示的含义为从指定的位置一直截取到最后。
语法:
SUBSTR('字符串',int,int)
/*从指定位置截取到最后*/
SUBSTR('字符串',int)
实例:
解析:使用substr方法,把14位的日期字符串截取前8位,只获取到前八位
5、GROUP BY 分组
说明:使用分组,将按照某个(或某些)字段进行分组,之后一般会配合其他的函数(比如count,sum等)对分组进行其他操作。
格式:
SELECT
分组字段
,其他字段
FROM
表名
GROUP BY
分组字段
注:在除MYSQL之外的数据库中,均需要按照如上的格式,即分组字段必须要单独写在SELECT(查询字段)之中。MYSQL比较特殊,可以不写分组字段,甚至还可以使用通配符"*",但此时只会显示此分组下的第一条记录。所以个人建议按照实际工作中使用的数据库语言按照规范书写代码
实例1
一般sql语言分组的使用
解析:首先使用分组,按照job分组,之后对每个分组的人数和薪资的总和统计计算。
实例2
MYSQL语言带“*”分组
解析:从图中可以看出,按照job进行分组,每个分组本应该有多个人,但此时只显示当前分组的第一个人的信息。不符合实际情况,不推荐使用。而对于其他的sql语言,这种写法会报错。
6、MAX(字段名)方法
说明:
此方法类似于上面实例中用到的COUNT()和SUM()方法,多用于在分组的基础上使用。此方法用于获取当前分组中某字段的最大值。当字段类型为数值时会获取最大值,当字段类型为字符串类型是会根据ASCII码比较获取最大值。
格式:
SELECT
分组字段
,MAX(其他字段)
FROM
表名
GROUP BY
分组字段
实例
需求:在emp表中,按照job分组,获取每个分组中最大sal的值
代码:
SELECT
job
,MAX(sal) AS 最大薪资
,MIN(sal) AS 最小薪资
FROM
emp
GROUP BY
job
结果展示:
注:同理MIN()函数是取当前分组某一字段的最小值
7、UPPER()函数
说明:此方法是用在将字符串字段的值全部修改为大写形式
语法:
UPPER(字段名)
实例:
在demo0001表中新增字段MEMO表示备注,其中写入大写小写不等字母
需求:查询MEMO列,并查询全大写与全小写列
代码:
SELECT
MEMO
,UPPER(MEMO) 大写
,LOWER(MEMO) 小写
FROM
demo0001
结果展示:
解析:对比三列可以看出差别,第一列是原数据,第二列是全大写,第三列是全小写(LOWER()函数)
8、in
说明:
in表示某一字段搜索范围的一个枚举值(枚举值之间使用逗号,间隔),当然这个枚举值也可能是一个子查询。相反的不想在这个枚举值区间的话使用not in。
语法:
字段 in (枚举值1,枚举值2,...)
字段 in (子查询)
字段 not in (枚举值1,枚举值2,...)
字段 not in (子查询)
实例
需求1:查询emp表中,job不为CEO,且dept为培优部和就业部的个人信息
代码:
SELECT
*
FROM
emp
WHERE
job not in ('CEO')
AND
dept in ('培优部','就业部')
结果展示:
需求2:查询CUS_NO在demo0001表中,且在emp表中dept不为就业部的个人信息
分析:需要两表联查,既需要CUS_NO存在于demo0001表中,又要满足在emp表中dept不为就业部,使用demo0001左连接emp表,满足条件的查询emp表中的信息
代码:
SELECT
emp.*
FROM
demo0001
LEFT JOIN
emp
ON demo0001.ID=emp.id
WHERE
dept not in ('培优部')
结果展示:
9、instr()函数
说明:
该函数在MYSQL和ORACLE中的用法不太相同
oracle中的用法:
instr(源字符串,目标字符串,起始位置,第几次出现)
其中有四个参数,分别表示源字符串,目标字符串,起始位置,第几次出现。源字符串是被查询的字符串,目标字符串是需要查询的某个字符串,起始位置表示从源字符串的第几位开始查询(初始为1),第几次出现是指定第几次目标字符串出现。instr()方法最后返回的是目标字符串在源字符串中出现的位置信息。
例如instr('JavaJavaJava','Java',1,2)表示Java这个目标字符串在JavaJavaJava这个源字符串从第1位开始,第2次出现的位置,最后返回的结果为5
mysql中的用法
instr(源字符串,目标字符串)中只存在两个参数,只能返回从头开始数第一次遇到的目标字符串的位置
例如instr('C++JavaJava','Java')表示的含义为Java这个目标字符串第一次在'C++JavaJava'这个目标字符串中的从左到右第一次出现的位置,返回结果为4
10、UNION 合并
说明:UNION会把多个字段相同的查询结果合并在一起,其中有相同的记录时,只会显示一条(去重操作)。如果不想执行去重操作的话需要使用UNION ALL。
注:需要保证两个查询结果的字段相同
语法:
查询1
UNION
查询2
举例1:
解析:由于需要两个查询最后的字段相同,所以给两个查询的字段添加别名,就可以拼接到一起
举例2:
使用UNION ALL和UNION合并两个查询的对比
解析:emp表中有id,demo0001表中有ID,其中有几个记录是相同的。最终通过UNION ALL合并后并没有去重。
解析:使用UNION后,重复的id被去掉。
11、字符串连接
说明:不同的sql语法中字符串连接的方式不同,MYSQL中一般使用CONCAT('字符串1','字符串2')函数拼接,db2中既可以使用CONCAT()函数,也可以使用||直接拼接字符串。
语法:
CONCAT('字符串1','字符串2')
/*db2中可以使用||*/
字符串1 || 字符串2
举例:
解析:从上面的举例中也可以看出来,如果一个字符串与NULL拼接,结果还是NULL
12、BETWEEN...AND...
说明:BETWEEN...AND...用在限定某一字段的范围在某个区间,一般是数字或时间
语法:
字段 BETWEEN 值1 AND 值2
实例
需求:查询emp表中,sal范围在3000(包含)到4500(包含)的且为培优部的全部信息
代码:
SELECT
*
FROM
emp
WHERE
dept='培优部'
AND
sal BETWEEN 3000 AND 4500
结果展示:
13、INTERVAL 用法
说明:INTERVAL后直接跟要减的 数(字符串类型)+单位(YEAR,MONTH,DAY)就可以完成对日期的计算。其中的天数可以为正数也可以是负数,负数代表日期向前推。
语法:
日期 INTERVAL '天数' DAY
日期 INTERVAL '天数' MONTH
日期 INTERVAL '天数' YEAR
举例:
解析:当前日期为2022年3月31日,刚好是这个月的最后一天,发现了一个有趣的现象,减1月的情况直接减到了2月28日,也就是2月的最后一天,不会出现2月31日这种情况
14、实战案例
接下来再通过一个实例将上面的知识点展示
这个代码分为两段
with sp as
(
select
c.busi_code 申请号,
max(b.SERIAL_NO) AS 顺序号
from
GCMS.CMS_AFW_FLOW_INFO_BAKI_H a
inner join
GCMS.CMA_AFW_FLOW_DTL_BAKI_H b
on a.flow_no=b.flow_no
inner join
GCMS.CMA_AFW_PKG_RELATION_H d
on a.busi_code=d.pkg_code
inner join
GCMS.CMA_AFW_DEAL_INFO_BAKI_H f
on f.flow_no=b.flow_no and f.serial_no=b.SERIAL_NO
inner join
GCMS.CMA_CORP_BUSI_INFO_ROUTER_H c
on c.busi_code=d.busi_code
left join
GCMS.GCM_MAG_AREA_H e
on b.area_code=e.area_code
left join
GCMS.GCM_SYS_DICT_DATA_H t0
on b.DEPARTMENT_CODE=t0.DICT_CODE
and t0.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t0.bean_code='MAG_DEPARTMENT#DEPARTMENT_CODE'
and b.area_code=t0.area_code
and upper(t0.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t1
on b.STATUS=t1.DICT_CODE
and t1.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t1.bean_code='AFW_TACHE_RESULT#TACHE_RESULT_CODE'
and upper(t1.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t2
on b.role_code=t2.DICT_CODE
and t2.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t2.bean_code='PLG_FLOW#TACH_CODE'
and upper(t2.LANG_CODE)='ZH_CN'
where
d.apply_type in ('88000001','88000002')
and
a.apply_type in ('88000008','88000022','88000020','88000001')
and
f.INFO_CODE='001'
and
f.INFO_VALUE like '%前置条件%'
and
f.INFO_VALUE LIKE '%管理要求%'
GROUP BY
c.busi_code
UNION
select
c.busi_code 申请号
max(b.AERIAL_NO) as "顺序号"
from
GCMS.CMA_AFW_FLOW_INFO_BAKI_H a
inner join
GCMS.CMA_AFW_FLOW_INFO_BAKI_H b
on a.flow_no=b.flow_no
inner join
GCMS.CMA_AFW_DEAL_INFO_BAKI_H f
on f.flow_no=b.flow_no and f.serial_no=b.serial_no
/*inner join
GCMS.CMA_AFW_PKG_RELATION_H d
on a.busi_code=d.pkg_code*/
inner join
GCMS.CMA_CORP_BUSI_INFO_ROUTER_H c
on c.busi_code=a.busi_code
left join
GCMS.GCM_MAG_AREA_H e
on b.area_code=e.busi_code
left join
GCMS.GCM_SYS_DICT_DATA_H t0
on b.DEPARTMENT_CODE=t0.DICT_CODE
and t0.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t0.bean_code='MAG_DEPARTMENT#DEPARTMENT_CODE'
and b.area_code=t0.area_code
and upper(t0.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t1
on b.STATUS=t1.DICT_CODE
and t1.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t1.bean_code='AFW_TACHE_RESULT#TACHE_RESULT_CODE'
and upper(t1.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t2
on b.role_code=t2.DICT_CODE
and t2.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t2.bean_code='PLG_FLOW#TACH_CODE'
and upper(t2.LANG_CODE)='ZH_CN'
where
/*d.apply_type in ('88000001','88000002')
and */
a.apply_type in ('88000008','88000022','88000020','88000001')
and
f.INFO_CODE='001'
and
f.INFO_VALUE like '%前置条件%'
and
f.INFO_VALUE LIKE '%管理要求%'
GROUP BY
c.busi_code
)
先不看第1行的with sp as,我们只看从第3行开始一直到第107行的代码部分
仔细观察可以发现这个是两个查询结果通过第55行的UNION拼接起来的结果,然后观察可以发现,这两个查询结果的结构也是非常相似。
1、查询字段部分只查询了两个字段,一个是整个查询的分组字段(对应到第52行和第53行)busi_code,另一个是通过MAX()查询到这个分组中最大的SERIAL_NO。
2、之后从第6行开始一直到第41行均是关联表包括左连接和内连接。
3、从第42行到第51行是条件语句。其中要将d表的apply_type控制在88000001和8800002之中(第43行),a表的apply_type控制在88000008、88000022、88000020、88000001之中(第45行)。第49行到第51行使用了like的模糊查询,要求INFO_VALUE这个字段的数据必须包含‘前置条件’或者‘管理要求’四个字。
4、后面UNION合并的表与此表非常相似,就不做赘述。唯独需要注意的是第68行到70行和第97行到98行是被注释掉的,sql种使用/**/,将包含在其中的内容表示注释掉。
5、最后说一说第一行的wish sp as,这个是一个标识,表示把之后括号中的所查询到的结果作为sp表在后续继续使用。
接着再来看这代码的第二段
SELECT DISTINCT
t0.TA200362011 AS "业务所在地区"
,t0.TA200362001 AS "企业代码"
,t0.TA200362053 AS "客户全称"
,t0.TA200362002 AS "借款合同号"
,t0.TA200362027 AS "贷款分类"
,t0.V_EMPNAME3 AS "主管信贷员姓名"
,t0.V_EMPNAME2 AS "最终签批人姓名"
,t0.TA200362012 AS "币种"
,t2.意见 as "意见"
FROM
ISFC.EDI_FHLOANMD_H t0
inner join
ISFC.CCM_TA200251_H t1
on t0.TA200362001=t1.TA200251001
left outer join
(
select
c.main_cis 客户编号
,account_code 合同号
,substr(f.INFO_VALUE,instr(f.INFO_VALUE,'管理要求',1,1)) AS "意见"
,c.busi_code 申请号
,b.SERIAL_NO AS 顺序号
from
GCMS.CMA_AFW_FLOW_INFO_BAKI_H a
inner join
GCMS.CMA_AFW_FLOW_DTL_BAKI_H b
on a.flow_no=b.flow_no
inner join
GCMS.CMA_AFW_PKG_RELATION_H d
on a.busi_code=d.pkg_code
inner join
GCMS.CMA_AFW_DEAL_INFO_BAKI_H f
on f.flow_no=b.flow_no and f.serial_no=b.serial_no
inner join
GCMS.CMA_CORP_BUSI_INFO_ROUTER_H c
on c.busi_code=d.busi_code
left join
GCMS.GCM_MAG_AREA_H e
on b.area_code=e.area_code
left join
GCMS.GCM_SYS_DICT_DATA_H t0
on b.DEPARTMENT_CODE=t0.DICT_CODE
and t0.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t0.bean_code='MAG_DEPARTMENT#DEPARTMENT_CODE'
and b.area_code=t0.AREA_CODE
and upper(t0.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t1
on b.STATUS=t1.DICT_CODE
and t1.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t1.bean_code='AFW_TACHE_RESULT#TACHE_RESULT_CODE'
and upper(t1.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t2
on b.role_code=t2.DICT_CODE
and t2.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t2.bean_code='PLG_FLOW#TACH_CODE'
and upper(t2.LANG_CODE)='ZH_CN'
where
d.apply_type in ('88000001','88000002')
and
a.apply_type in ('88000008','88000022','88000020','88000001')
and
f.INFO_CODE='001'
and
f.INFO_VALUE like '%前置条件%'
and
f.INFO_VALUE LIKE '%管理要求%'
UNION
select
c.main_cis 客户编号
,c.account_code 合同号
,substr(f.INFO_VALUE,instr(f.INFO_VALUE,'管理要求',1,1)) AS "意见"
,c.busi_code 申请号
b.SERIAL_NO AS 顺序号
from
GCMS.CMA_AFW_FLOW_INFO_BAKI_H a
inner join
GCMS.CMA_AFW_FLOW_DTL_BAKI_H b
on a.flow_no=b.flow_no
inner join
GCMS.CMA_AFW_DEAL_INFO_BAKI_H f
on f.flow_no=b.flow_no and f.serial_no=b.serial_no
/*inner join
GCMS.CMA_AFW_PKG_RELATION_H d
on a.busi_code=d.pkg_code*/
inner join
GCMS.CMA_CORP_BUSI_INFO_ROUTER_H c
on c.busi_code=a.busi_code
left join
GCMS.GCM_MAG_AREA_H e
on b.area_code=e.area_code
left join
GCMS.GCM_SYS_DICT_DATA_H t0
on b.DEPARTMENT_CODE=t0.DICT_CODE
and t0.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t0.bean_code='MAG_DEPARTMENT#DEPARTMENT_CODE'
and b.area_code=t0.AREA_CODE
and upper(t0.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t1
on b.STATUS=t1.DICT_CODE
and t1.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t1.bean_code='AFW_TACHE_RESULT#TACHE_RESULT_CODE'
and upper(t1.LANG_CODE)='ZH_CN'
left join
GCMS.GCM_SYS_DICT_DATA_H t2
on b.role_code=t2.DICT_CODE
and t2.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and t2.bean_code='PLG_FLOW#TACH_CODE'
and upper(t2.LANG_CODE)='ZH_CN'
where
/*d.apply_type in ('88000001','88000002')
and */
a.apply_type in ('88000008','88000022','88000020','88000001')
and
f.INFO_CODE='001'
and
f.INFO_VALUE like '%前置条件%'
and
f.INFO_VALUE LIKE '%管理要求%'
)t2
on t0.TA200362002=t2.合同号
left outer join
sp
on sp.申请号=t2.申请号
where
t1.TA200251008='01'
and
t0.TA200362013 not like '0715%'
and
t0.TA200362013 not like '0117028%'
and
t0.TA200362013 not like '0119007%'
AND
t0.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and
t1.work_dt=substr(to_char(ADD_MONTHS(CURRENT_TIMSTAMP(0),-1),'YYYYMMDD'),1,6)
and
t0.TA200362092 between substr(to_char(CURRENT_TIMSTAMP(0)-interval '1 month','YYYYMMDD'),1,6)||'01' and to_char((substr(to_char(CURRENT_TIMSTAMP(0),'YYYYMMDD'),1,6)||'01')::date-1,'YYYYMMDD')
and
sp.顺序号=t2.顺序号
)
有了上面一段代码的铺垫,其实这一段也就变得非常简单了
1、首先,这张表整体的结构为。从第1行到第10行是查询的条件,并且最终结果种重复的记录会被剔除。从第11行开始到第131行使馆量的表总共有t0,t1,t2和sp表。最后是查询的条件。
2、其中t2表的结构又是通过UNION合并起来的结果,这个里面的一些知识点我们单独说。
3、第44行看似这个很长,但实际上通过拆解其中的语句,也能很快理解其中的含义。从最里面看,首先通过CURRENT_TIMESTAMP(0)获取当前的日期,之后是通过ADD_MONTH将时间往前退一个月,在将这个日期转变为YYYYMMDD格式的字符串,最后通过截取前六位,获取当前的年和月的信息。同理这一段的第51行和第57行也是同样的操作。
4、合并下半段中的查询条件的第78行,还是从里向外看,首先是用instr方法获取INFO_VALUE从第一位开始,‘管理要求’这四个字第一次出现的位置。之后通过substr从这个位置开始截取到这个字段的最后。
5、在第129行到第131行,使用到了上面定义的sp表进行左外连接。
6、最后的一个查询条件使用了BETWEEN...AND...关键字(第145行),含义选取的日期的在是在定义的两个日期之内。起始日期是获取当前日期后使用INTERVAL关键字减去1个月,最后通过TO_CHAR和SUBSTR关键字获取日期的年月,最后||拼接01表示这个月的第一天。