WHERE 条件 column 为纯英文字符 或 不包含任何字符
语法：
SELECT * FROM your_table WHERE REGEXP(your_column,'^[A-Za-z]+$');
SELECT * FROM your_table WHERE NOT REGEXP(your_column,'^[A-Za-z]+$');
例：
SELECT DISTINCT t.pldlibho FROM kibb_pldlyw t WHERE REGEXP_LIKE(t.pldlibho, '[a-zA-Z]+$');
-- 不包含任何英文字符
SELECT DISTINCT t.pldlibho FROM kibb_pldlyw t WHERE NOT REGEXP_LIKE(t.pldlibho, '[a-zA-Z]+$');
WHERE 条件 column 为纯数字 或 不包含任何数字
语法：
SELECT * FROM your_table WHERE REGEXP(your_column,'^[0-9]+$');
SELECT * FROM your_table WHERE NOT REGEXP(your_column,'^[0-9]+$');
例：
SELECT DISTINCT t.pldlibho FROM kibb_pldlyw t WHERE REGEXP_LIKE(t.pldlibho, '^[0-9]+$');
--不包含任何数字
SELECT DISTINCT t.pldlibho FROM kibb_pldlyw t WHERE NOT REGEXP_LIKE(t.pldlibho, '^[0-9]+$');
PARTITION BY 的简单使用
部分内容转载至：
https://blog.csdn.net/weixin_44711823/article/details/135966741?fromshare=blogdetail&sharetype=blogdetail&sharerId=135966741&sharerefer=PC&sharesource=FuTian0715&sharefrom=from_link
更详细的讲解请点击链接查看。
说明：
partition by窗口函数 和 group by分组的区别：
partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录。
partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
partition by与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。
partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似excel中的操作），而group by则只保留参与分组的字段和聚合函数的结果; 简单来说窗口函数对部分数据进行排序、计算等操作，group by对一组值进行聚合，即窗口函数是每一行都会保留，group by是从多行浓缩为少数行。
语法：
<窗口函数> over ( partition by<用于分组的列名> order by <用于排序的列名>)
窗口函数：
专用窗口函数: rank(), dense_rank(), row_number()
聚合函数 : sum(), max(), min(), count(), avg() 等
例：
SELECT m.qyuezhao,CASE m.dxnqyzhtWHEN '3' THEN'欠费'WHEN '4' THEN'暂停'ENDFROM (SELECT Row_number() over(PARTITION BY t.dxnqyzht ORDER BY t.qyuezhao) rn,t.qyuezhao,t.dxnqyzhtFROM kibb_dxinqy tWHERE t.dxnqyzht IN ('3', '4')) mWHERE rn <= 10;
SELECT *,rank() over(partition by type order by price desc) as mm from commodity;
SELECT *,row_number() over(partition by type order by price desc) as mm from commodity;
SELECT *,dense_rank() over(partition by type order by price desc) as mm from commodity;
从以上结果来看:
rank()函数:如果存在并列名次的行,会占用下一个名次的位置,比如苹果的组内排名 1,2,3,4, 但是由于有两个是并列的,所以显示的排名是 1,1,3,4 ,其中 2 的位置还是被占用了
row_number()函数:不考虑并列的情况,此函数即使遇到了price 相同的情况,还是会默认排出一个先后来
dense_rank()函数:如果存在并列名次的行,不会占用下一个名次的位置,例如图片的最后显示的是 1,1,2,3
json格式字符串处理相关函数
IS JSON
判断某个字段是否为有效json
select * from kapb_jioybw bw where bw.quanjuls = 'GFTS002021051100001685916' and bw.jiaoyirq = '20210511';
JSON_VALUE
JSON_VALUE只支持scalar value，即只返回一行一列，通常用在select语句或where条件中。
JSON_VALUE接受两个参数，即JSON文档(document)和到指定属性的路径(path)，返回值可以格式化。
JSON_VALUE支持错误处理，例如当指定的path不正确（如路径不存在，大小写不匹配），返回多个值（非scalar）时。错误处理方式有3种，默认为返回空值（NULL ON ERROR），其它为返回指定默认值（DEFAULT on ERROR），报错（ERROR ON ERROR）。
NULL演示
-- 错误示例1：Address返回多个属性，不是scalar. Address改为Address.city就正确了。
select JSON_VALUE(xnybwvar ,'$.ShippingInstructions.Address')from kapb_jioybw pwhere JSON_VALUE(xnybwvar ,'$.PONumber' returning NUMBER(10)) = 1and p.quanjuls = 'GFTS002021051100001685917' and p.jiaoyirq = '20210511';
-- 错误示例2：大小写错误，Name应为name
select JSON_VALUE(xnybwvar ,'$.ShippingInstructions.Name')from kapb_jioybw pwhere JSON_VALUE(xnybwvar ,'$.PONumber' returning NUMBER(10)) = 450and p.quanjuls = 'GFTS002021051100001685917' and p.jiaoyirq = '20210511';
-- DEFAULT on ERROR演示
-- ERROR ON ERROR演示
-- 以上所的错误处理只针对运行时错误，例如以下Address前的.写成了,号，则不在以上所说错误处理的范畴：
JSON_QUERY
JSON_QUERY是JSON_VALUE的补充，参数个数与类型与其一样，但可返回一个对象或array。
输出中省去了很多空格，但为了美观，你也可以加PRETTY关键字以添加缩进和对齐：
下例为使用array index以返回array中的一个对象：
注意JSON_QUERY只能返回对象，如果path指定是一个scalar值，则默认返回空值，例如：
JSON_QUERY的错误处理有三种，默认为NULL ON ERROR, ERROR ON ERROR与JSON_VALUE类似，EMPTY ON ERROR返回空的array。
最常见的错误是返回值不是object或array，而是scalar值。
不过还有一种特殊方式，可将scalar转换为array：
利用WITH ARRAY WRAPPER将结果强制转换为array
JSON_TABLE
第一个查询显示了如何从文档中最多发生一次的值投射一组列。值可能来自任何级别的嵌套，只要它们不来自组的键，或来自阵列的后裔，除非使用索引来识别数组中的一个项目。
select M.*from kapb_jioybw p,JSON_TABLE(p.xnybwvar,'$' columns PO_NUMBER NUMBER(10) path '$.PONumber',REFERENCE VARCHAR2(30 CHAR) path '$.Reference',REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',USERID VARCHAR2(10 CHAR) path '$.User',COSTCENTER VARCHAR2(16 CHAR) path '$.CostCenter',TELEPHONE VARCHAR2(16 CHAR) path'$.ShippingInstructions.Phone[0].number') Mwhere p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'and PO_NUMBER between 1 and 2;
如果输出的列仍是对象或array，则可使用嵌套（NESTED PATH）：
select M.*from kapb_jioybw p,JSON_TABLE(p.xnybwvar,'$' columns(PO_NUMBER NUMBER(10) path '$.PONumber',REFERENCE VARCHAR2(30 CHAR) path '$.Reference',REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',USERID VARCHAR2(10 CHAR) path '$.User',COSTCENTER VARCHAR2(16) path '$.CostCenter',NESTED PATH '$.LineItems[*]'columns(ITEMNO NUMBER(16) path '$.ItemNumber',DESCRIPTION VARCHAR2(32 CHAR) path'$.Part.Description',UPCCODE VARCHAR2(14 CHAR) path'$.Part.UPCCode',QUANTITY NUMBER(5, 4) path '$.Quantity',UNITPRICE NUMBER(5, 2) path'$.Part.UnitPrice'))) Mwhere p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'and PO_NUMBER between 1 and 2;
JSON_TABLE常用于建立关系型视图，然后可以用标准的SQL语句操作。
create or replace view json_test_view
as
select M.*from kapb_jioybw p,JSON_TABLE(p.xnybwvar,'$' columns(PO_NUMBER NUMBER(10) path '$.PONumber',REFERENCE VARCHAR2(30 CHAR) path '$.Reference',REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',USERID VARCHAR2(10 CHAR) path '$.User',COSTCENTER VARCHAR2(16) path '$.CostCenter',NESTED PATH '$.LineItems[*]'columns(ITEMNO NUMBER(16) path '$.ItemNumber',DESCRIPTION VARCHAR2(32 CHAR) path'$.Part.Description',UPCCODE VARCHAR2(14 CHAR) path'$.Part.UPCCode',QUANTITY NUMBER(5, 4) path '$.Quantity',UNITPRICE NUMBER(5, 2) path'$.Part.UnitPrice'))) Mwhere p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'and PO_NUMBER between 1 and 2;
定义完这些视图后，开发者就可以完全利用SQL的能力了。
JSON_EXISTS
用在where语句中，和EXISITS类似，测试JSON document中是否存在指定的path。
select count(*)from kapb_jioybw pwhere JSON_EXISTS(p.xnybwvar, '$.ShippingInstructions.Address.state')and p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'
JSON_EXISTS可以区分key不存在或key存在，value不存在或为空的情形，试比较以下输出：
select JSON_VALUE(p.xnybwvar, '$.ShippingInstructions.Address.county'),count(*)from kapb_jioybw pwhere p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'group by JSON_VALUE(p.xnybwvar, '$.ShippingInstructions.Address.county');
select JSON_VALUE(p.xnybwvar, '$.ShippingInstructions.Address.county'),count(*)from kapb_jioybw pwhere p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511'and JSON_EXISTS(p.xnybwvar, '$.ShippingInstructions.Address.county')group by JSON_VALUE(p.xnybwvar, '$.ShippingInstructions.Address.county');
JSON_EXISTS还支持predicate，就是可以带条件。
select p.xnybwvarfrom kapb_jioybw pwhere JSON_EXISTS(p.xnybwvar,'$?(@.PONumber == $PO_NUMBER)' passing 1 as "PO_NUMBER")and p.quanjuls = 'GFTS002021051100001685917'and p.jiaoyirq = '20210511';
SELECT COUNT(1)FROM kapb_jioybw pWHERE JSON_EXISTS(p.xnybwvar,'$?(@.PONumber == $PO_NUMBER)' passing 1 AS "PO_NUMBER")AND p.quanjuls = 'GFTS002021051100001685917'AND p.jiaoyirq = '20210511';
JSON索引
使用JSON_VALUE创建的唯一索引，基于的值必须是scalar，而且必须唯一。可以是B-Tree索引或Bitmap索引。
create unique index PO_NUMBER_IDXon kapb_jioybw p (JSON_VALUE(p.xnybwvar,'$.PONumber' returning NUMBER(10) ERROR ON ERROR NULL ON EMPTY))
