1.当字段为,分割需要取对应的值 案例*
第一种:incomeInfo.income_class_code值为 "00010001,00080042"
可以采用第一种:IF(rtp.RIGHT_NUMBER = 1, SUBSTRING_INDEX(incomeInfo.income_class_code, ',', 1), SUBSTRING_INDEX(incomeInfo.income_class_code, ',', -1))
如果rtp.right_number值为1取incomeInfo.income_class_code的值是"00010001"否则取最后一位"00080042"
第二种:incomeInfo.income_class_code值为 "00010001,00080042,00060067"
可以采用第二种:
SUBSTRING_INDEX(SUBSTRING_INDEX(incomeInfo.INCOME_CLASS_CODE,',',rtp.RIGHT_NUMBER),',',-1)
当rtp.RIGHT_NUMBER为1时,incomeInfo.INCOME_CLASS_CODE取的值是"00010001"
当rtp.RIGHT_NUMBER为2时,incomeInfo.INCOME_CLASS_CODE取的值是"00080042"
当rtp.RIGHT_NUMBER为3时,incomeInfo.INCOME_CLASS_CODE取的值是"00060067"
2.分组WM_CONCAT与distinct和group by搭配写法 案例*
-- 多主键表时，假设SRUDENT表主键是STUDENT_ID和ID,肯定有一个主键重复另一个主键不重复的数据呀
ID STUDENT_ID NAME AGE
1 1001 张三 20
1 1002 李四 21
1 1003 张三 22
2 1001 王五 25
-- 然后编写sql语句 去重的话根据业务来，我这边需求要去重所以才加上，并且WM_CONCAT是转变为Text文本类型，我这边还要to_char将他变成字符串类型
select
ID,
to_char(WM_CONCAT(distinct NAME)) AS USER_NAME,
to_char(WM_CONCAT(distinct AGE)) AS AGE
from STUDENT
group by ID;
-- 此时查出来的效果是 分组后自动,分割
ID NAME AGE
1 张三,李四 20,21,22
2 王五 25
3.如果这样否则那样CASE WHEN ID=1 THEN 这样 ELSE 那样 END AS NAME 案例*
-- 依旧是多主键 ID和STUDENT_ID
ID STUDENT_ID NAME AGE
1 1001 张三 20
1 1002 李四 21
1 1003 张三 22
2 1001 王五 25
SELECT
CASE WHEN ID=1 THEN SRUDENT_ID||'_'||NAME ELSE NAME AS USER_NAME,
AGE
FROM STUDENT
-- 效果
USER_NAME AGE
1001_张三 20
1002_李四 21
1003_张三 22
王五 25
SELECT
(CASE
WHEN GENDER='男' THEN 200||STU_NAME
WHEN GENDER='女' THEN 201||STU_NAME
ELSE 404||STU_NAME END
) AS STU_CARD ,
AGE
FROM STUDENT
-- 效果
STU_CARD AGE
200张哥哥 20
201李美丽 22
404妖妖妖 21
4.FIND_IN_SET精准查询值，比like查询效率高又准确 案例*
ID MAN_ID
1001 10,20,30
1002 10,30,50
-- 准确查到id为1001并且man_id中有10的
SELECT ID
FROM STUDENT
WHERE ID=1001 AND FIND_IN_SET('10',MAN_ID)
-- 效果
ID
1001
-- 准确查到id为1001并且man_id中有1的
SELECT ID
FROM STUDENT
WHERE ID=1001 AND FIND_IN_SET('1',MAN_ID)
-- 效果
ID
空值没查询到
思路：如果用like查询man_id中有1的那能查询到，但是10中有1，符合带有1这个值都能查到，不符合想要的结果。在保存man_id数据时值,分割。查询数据不精准。如果利用 FIND_IN_SET('1',man_id) 是能查询必定为1的,值是10不算，查询结果空值。符合想要的结果，所以可以用这种方式
5.IFNULL为空赋值 案例*
-- 数据库数据
ID NAME AGE
1 20
2 李四 21
3 王五 22
SELECT ID,IFNULL(NAME,'/') AS NAME FROM STUDENT
-- 效果
ID NAME
1 /
2 李四
3 王五
6.Listagg拼接查询出来的数据按指定格式，比如/，并使用REGEXP_REPLACE函数去重 案例*
-- 人员类型是0001权利人的并且ID是202309260003的,符合要求的所有数据进行/拼接,拿到拼接好的人员姓名
select LISTAGG(ISNULL(MI.MAN_NAME,''),'/')WITHIN GROUP (ORDER BY MI.MAN_ID ASC) rightManName
from MAN_INFO MI ,RIGHT_INFO RRI
where MI.ID=RRI.ID AND MI.RIGHT_NUMBER=RRI.RIGHT_NUMBER AND MI.MAN_CLASS='0001'
and RRI.ID='202309260003';
--效果：
rightManName
张三/李四/王五/李四
-- 根据正则表达式进行对拼接好的人员姓名去重
select
REGEXP_REPLACE(
LISTAGG(ISNULL(MI.MAN_NAME,''),'/')WITHIN GROUP (ORDER BY MI.MAN_ID ASC),
'([^/]+)(/\1)+', '\1')
as rightManName
from MAN_INFO MI ,RIGHT_INFO RRI
where MI.ID=RRI.ID AND MI.RIGHT_NUMBER=RRI.RIGHT_NUMBER AND MI.MAN_CLASS='0001'
and RRI.ID='202309260003';
-- 效果：把重复叫张三的给去重了
rightManName
张三/李四/王五
7.LEFT JOIN左连接应用 案例*
SELECT DISTINCT
RII.ID AS YWH,
QLR.QLSX QLRSX,
QLR.RYMC AS QLRMC,
RRTP.PRO_TYPE AS PTYPE
FROM INCOME_INFO RII
INNER JOIN RIGHT_TO_PROPERTY RRTP ON RII.ID=RRTP.ID
LEFT JOIN (
SELECT DISTINCT
RY.ID AS YWH,
RY.RIGHT_NUMBER AS QLSX,
REGEXP_REPLACE(LISTAGG(RY.MAN_NAME, '/') WITHIN GROUP(ORDER BY RY.MAN_NAME), '([^/]+)(/\1)+', '\1') AS RYMC
FROM MAN_INFO RY
LEFT JOIN MAN_RELATION GL ON RY.ID=GL.ID AND RY.MAN_ID=GL.RELATION_MAN_ID
LEFT JOIN MAN_INFO FR ON GL.ID=FR.ID AND GL.MAN_ID=FR.MAN_ID AND FR.MAN_CLASS IN ('0005','0006')
WHERE RY.MAN_CLASS ='0001' AND RY.DELETE_DATE IS NULL
GROUP BY RY.ID,RY.RIGHT_NUMBER
) AS QLR ON QLR.YWH=RRTP.ID AND QLR.QLSX=RRTP.RIGHT_NUMBER
WHERE RII.Status_Code='0002' --状态为0002
and RII.DELETE_DATE IS NULL
and RRTP.DELETE_DATE IS NULL
and RII.ID like '2005%' --id编写是年月日+4位当天顺序 比如200501010001
limit 0,1000;
-- 效果:
YWH QLRSX QLRMC PTYPE
200501090001 1 张三 0001
200503080012 2 李四 0003
200509050001 1 王五 0001
8.根据模式名查询该模式下所有表名称 案例*
-- 根据模式名为DREMIS_CODE,查询出该模式下所有表名称
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'DREMIS_CODE';
-- 效果：
TABLE_NAME
CODE_MAN_CLASS
CODE_RIGHT_TYPE
CODE_PAYER
9.字符串截取SUBSTRING从第几位截取几个 案例*
SELECT SUBSTRING('130208007006', 7, 3)
--效果:从第7位开始截取 截取3个
007
10.将查询到的结果排序并自增 案例*
-- 根据student表的stu_id主键排序，将符合要求的数据进行模拟自增
SELECT
(ROW_NUMBER() OVER(ORDER BY STU.STU_ID)) AS BSM,
STU_NAME AS MC
FROM STUDENT AS STU
WHERE STU_NAME LIKE '张%'
--效果
BSM MC
1 张三
2 张某
3 张总
4 张工
5 张先生
-- 自定义初始值 如果(ROW_NUMBER() OVER(ORDER BY STU.STU_ID))目前值是1，那么+100就是101
SELECT
(ROW_NUMBER() OVER(ORDER BY STU.STU_ID))+100 AS BSM,
STU_NAME AS MC
FROM STUDENT AS STU
WHERE STU_NAME LIKE '张%'
--效果
BSM MC
101 张三
102 张某
103 张总
104 张工
105 张先生
-- 查出来数据库最大的STU_ID值 并使当前的自增数据进行相加
SELECT (ROW_NUMBER() OVER(ORDER BY STU_ID))+(SELECT max(STU_ID) from student) AS BSM FROM STUDENT;
-- 效果: 假设查出来最大的值是105 (ROW_NUMBER() OVER(ORDER BY STU_ID))假设是1
BSM
106
107
108
109
110
11.查询语句虚拟列的应用 案例*
-- Student表只有字段 stu_id,stu_name,stu_age 然后查询语句可以创建虚拟列进行查询展示 其中||能把两边拼接
SELECT
STU_ID,STU_NAME,STU_AGE,(STU_ID||STU_NAME) AS CARD
FROM STUDENT
-- 效果
STU_ID STU_NAME STU_AGE CARD
1 张三 20 1张三
2 李四 21 2李四
12.将字符串转换成数字类型 案例*
SELECT CAST('202307070001' AS BIGINT) AS income_id;
-- 效果: 最终返回bigint类型
income_id
202307070001
13.取数据库ID的最大值 假设数据库id是字符串类型的情况 案例*
-- 查询当前数据库最大的ID值是多少
-- 方案1 根据id倒序排序的第一条就是最大的 并转换成整数类型 不推荐根据业务需要来
SELECT CAST(ID as bigint) FROM STUDENT ORDER BY ID DESC LIMIT 1;
-- 方案2 max函数取当前表中最大的值 推荐
SELECT max(ID) from STUDENT;
-- 当student表数据为空时max函数会返回null，我这边要把null设置0并且通过cast指定类型为bigint整数类型
SELECT CAST(ifnull(max(STU_ID),0) AS bigint) AS BSM FROM STUDENT;
-- 效果：整数类型的0
BSM
0
14.随机数/四舍五入/整数设置范围 案例*
SELECT ROUND(1.3); -- 输出结果：1
SELECT ROUND(1.8); -- 输出结果：2
SELECT ROUND(1.2323,3); -- 输出结果：1.232
SELECT ROUND(1.2328,3); -- 输出结果：1.233
-- 获取1~10的随机整数
SELECT FLOOR( 1+(RAND() * 10));
-- 获取1~10之间不包括10的随机浮点数
SELECT 1 + (RAND() * 9);
SELECT ROUND(1 + (RAND() * 9),3);-- 随机浮点数 最多小数点后3位并且四舍五入
8374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
