0727
ACCT_账户
第一篇
DELETE FROM a WHERE a.data_dt = ${V_DT};
INSERT INTO a (ziduan1,ziduan2,ziduan3)
SELECT %{V_DT} AS ziduan1,a.ziduan2,a,ziduan3,
FROM b
WHERE b.data_dt = ${V_DT};
第二篇
DELETE FROM a WHERE a.data_dt = ${V_DT};
# 创建临时表 应计利息
DROP TABLE IF EXISTS 应计利息;
CREATE TEMP TABLE 应计利息 DISTRIBUTED BY HASH(字段一,字段二)
AS
SELECT t4.字段一,t4.字段二,
SUM(CASE
WHEN t5.m IN ('123','456') THEN t5.n
WHEN t5.m IN ('147','258') THEN NVL(t5.o,0) * -1
ELSE (t5.n + NVL(t5.o,0)) * -1
END) AS amount
FROM FSFJSOF t5
INNER JOIN (SELECT t6.a,
t7.b,
t6.c,
t6.d || TRIM (TO_CHAR(t7.e,'009')) AS f
FROM t6
INNER JOIN t7
ON t6.* = t7.*
AND t6.** = **
AND t7.** = **
WHERE t6.data_dt = ${V_DT}) AS t4
ON t5.** = t4.**
WHERE t5.** IN ('11','22','33','44','55')
AND t5.data_dt = ${V_DT}
# 创建临时表
DROP TABLE IF EXISTS *;
CREATE TEMP TABLE * DISTRIBUTE BY HASH(*,*)
AS
SELECT *,*,*
FROM t9,
(SELECT *,*
FROM *
WHERE * AND *
GROUP BY 1,2) AS t10
WHERE t9.* = t10.* ;
# 创建临时表
DROP TABLE IF EXISTS *;
CREATE TEMP TABLE * DISTRIBUTE BY HASH(*)
AS
SELECT *,*,*,*,
CASE
WHEN * = '123' THEN '1'
WHEN * = '456' THEN '2'
ELSE *
END AS *,
CASE
WHEN SUBSTR(*,1,4) IN ('1234','5678') AND t5.org_term_mult = 'D' THEN
TO_CHAR(TO_DATE(t1.*,'YYYYMMDD')+t5.org_term,'YYYYMMDD')
WHEN SUBSTR(*,1,4) IN ('1472','2583') AND t5.org_term_mult = 'M' THEN
TO_CHAR(ADD_MONTHS(TO_DATE(t1.*,'YYYYMMDD'),t5.org_term),'YYYYMMDD')
WHEN SUBSTR(*,1,4) IN ('4563','7896') AND t5.org_term_mult = 'Y' THEN
TO_CHAR(ADD_MONTHS(TO_DATE(t1.*,'YYYYMMDD'),t5.org_term*12),'YYYYMMDD')
ELSE t1.*
END AS *
FROM t1 JOIN t2 ON t1.* = t2.* AND t2.data_dt = ${V_DT}
LEFT JOIN (select……) t3 ON t2.* = t3.* AND t2.* = t3.*
LEFT JOIN t4 ON t1.* = t4.* AND t4.data_dt = ${V_DT}
LEFT JOIN t5 ON t1.* = t5.* AND SUBSTR(t1,1,4) = t5.*
WHERE t1.* in('1','2','3','4') AND t1.* = * AND t1.data_dt = ${V_DT}
# 还有三个临时表!
# 创建临时表
DROP TABLE IF EXISTS *_temp;
CREATE TEMP TABLE *_temp DISTRIBUTE BY HASH(*)
AS
SELECT a.*,c.*
FROM a
JOIN b ON a.* = b.*
AND NVL(b.*,0) <> '1' ###就是这里,加and和使用where有什么区别
AND b.data_dt = ${V_DT}
JOIN c ON a.* = c.*
AND c.data_dt = ${V_DT}
WHERE SUBSTR(a.*,1,4) = '1234'
AND a.* = *
AND a.data_dt = ${V_DT}
# 创建临时表
# 创建临时表
#这两个和上边是一样的套路
#写入目标表
INSERT INTO schema_name.table_name
(a,b,c,d,e,f,g)
SELECT * AS a,
* AS b,
* AS c,
* AS d,
* AS e,
* AS f
* AS g
FROM t1
LEFT JOIN table_m
ON t1.* = table_m.*
AND table_m.data_dt=${V_DT}
LEFT JOIN table_n
ON t1.* = table_n.*
;
函数学习
1.trim 可以过滤指定的字符串
trim ( [ {BOTH|LEADING|TRAILING} [remstr] FROM] str)
- both 删除指定的首位字符
- leading 删除指定的首字符
- trailing 删除指定的尾字符
若分类符BOTH\LEADING\TRAILING没有一个是给定的,则默认为BOTH。
若remstr(要删除的字符)未被指定,则默认删除空格。
栗子
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); --删除指定的首字符 x
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); --删除指定的首尾字符 x
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); --删除指定的尾字符 x
-> 'barx'
LTRIM(str) #去除左边的空格
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
RTRIM(str) #去除右边的空格
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
2.连接符 ||
比如’111 || 222’ 其结果就是’111222’.
3.to_char(num,009)
后边是009,即最后输出的为三位数,如果不够三位,则前边补0.
4.SUBSTR(string,int a ,int b)
string为需要截取的字符串,a截取字符串开始的位置,b要截取的长度。如果省略b,则直接截取到最后。
栗子
Select substr(‘helloworld’,-4,3) value from daul;
返回结果为orl
问题
Q:join 后边的on 所起的作用,和where语句是一样的吗?
A://left join和right join
表连接语句后边的on 是在执行两表连接的过程中,用来生成中间表的。无论on后边的条件是否为真,均会按照左连接或右连接来返回。即 如果是左连接,那么生成的中间表就包含所有左表的记录;右连接同理。
而where是在生成中间表之后,对整个中间表进行过滤的条件。这时已经没有左连接的含义(最后要包含左表所有的记录)了,是对整个临时表的约束,如果条件不为真,则全部过滤掉。
//join 或 inner join
对于inner join (等价于join),on后边的连接条件和where语句后的条件是一样的功能。本项目中用到的是join,所以条件放在on后边和放在where后边是一样的。
见on和where的区别
今日学习结束,明天奥利给,记得做到专心和高效!跑神的话就赶快跑回来!记住用心工作,用心生活。