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.nWHEN t5.m IN ('147','258') THEN NVL(t5.o,0) * -1ELSE (t5.n + NVL(t5.o,0)) * -1END) AS amount
FROM FSFJSOF t5
INNER JOIN (SELECT t6.a,t7.b,t6.c,t6.d || TRIM (TO_CHAR(t7.e,'009')) AS fFROM t6 INNER JOIN t7ON 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 *,CASEWHEN SUBSTR(*,1,4) IN ('1234','5678') AND t5.org_term_mult = 'D' THENTO_CHAR(TO_DATE(t1.*,'YYYYMMDD')+t5.org_term,'YYYYMMDD')WHEN SUBSTR(*,1,4) IN ('1472','2583') AND t5.org_term_mult = 'M' THENTO_CHAR(ADD_MONTHS(TO_DATE(t1.*,'YYYYMMDD'),t5.org_term),'YYYYMMDD')WHEN SUBSTR(*,1,4) IN ('4563','7896') AND t5.org_term_mult = 'Y' THENTO_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

问题

1.join 后边的on 所起的作用，和where语句是一样的吗?

今日学习结束，明天奥利给，记得做到专心和高效！跑神的话就赶快跑回来！记住用心工作，用心生活。