每日汇总

最新推荐文章于 2024-10-25 21:41:21 发布

原创最新推荐文章于 2024-10-25 21:41:21 发布 · 199 阅读

0 ·

CC 4.0 BY-SA版权

每日学习汇总专栏收录该内容

3 篇文章

订阅专栏

本文深入探讨SQL技巧，包括数据操作语言（DML）的DELETE和INSERT语句的使用，复杂临时表的创建与数据筛选，以及函数如TRIM、to_char、SUBSTR的运用。通过实例解析，帮助读者理解SQL在数据处理中的强大能力。

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的区别

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