实用SQL小总结

🕗 发布于 2024-09-29 22:41 sql

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.dxnqyzht
         WHEN '3' THEN
          '欠费'
         WHEN '4' THEN
          '暂停'
       END
  FROM (SELECT Row_number() over(PARTITION BY t.dxnqyzht ORDER BY t.qyuezhao) rn,
               t.qyuezhao,
               t.dxnqyzht
          FROM kibb_dxinqy t
         WHERE t.dxnqyzht IN ('3', '4')) m
 WHERE 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 p
 where JSON_VALUE(xnybwvar ,'$.PONumber' returning NUMBER(10)) = 1
 and p.quanjuls = 'GFTS002021051100001685917' and p.jiaoyirq = '20210511';

在这里插入图片描述

-- 错误示例2：大小写错误，Name应为name
select JSON_VALUE(xnybwvar ,'$.ShippingInstructions.Name')
 from kapb_jioybw p
 where JSON_VALUE(xnybwvar ,'$.PONumber' returning NUMBER(10)) = 450
 and 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') M
 where 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'))) M
 where 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'))) M
 where 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 p
 where 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 p
 where 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 p
 where 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.xnybwvar
 from kapb_jioybw p
 where 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 p
 WHERE 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_IDX
   on kapb_jioybw p (
         JSON_VALUE(
            p.xnybwvar,'$.PONumber' returning NUMBER(10) ERROR ON ERROR NULL ON EMPTY
         )
      )

在这里插入图片描述

原文地址：https://blog.csdn.net/FuTian0715/article/details/142633829

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：git | 合并 commit 的两种方法
下一篇：芝法酱学习笔记（0.5）——使用jenkins做自动打包

恋爱辅助应用小程序app开发之广告策略
恋爱话术小程序带流量主广告开启，是一个有效的盈利模式，可以增加小程序的收入来源。以下是对此的详细分析。：根据小程序的特点和目标用户群体，选择合适的广告类型（如横幅广告、插屏广告、视频广告等）。：合理的
阅读更多2024-09-30
Vue.js与Flask/Django后端的协同开发研究
前后端分离是指将客户端（前端）和服务器端（后端）进行逻辑上的分离。前端使用现代JavaScript框架（如Vue.js、React、Angular等）进行用户界面开发，后端则使用如Node.js、Fl
阅读更多2024-09-30
【HTML5】html5开篇基础（5）
选择控件按钮控件 input标签常用属性下面是一个使用的示例：在这个例子中，点击"请输入用户名："文本会使元素获得焦点。
阅读更多2024-09-30
2024大二上js高级+ES6学习9.26（闭包，递归函数）
9.26.2024。
阅读更多2024-09-30
华为eNSP：端口隔离
端口隔离是一种网络配置技术，用于将不同的网络设备或用户隔离在不同的虚拟局域网（VLAN）中，以实现网络流量的隔离和安全性提升。通过在交换机或路由器上配置端口隔离，可以将连接到同一设备的端口分配到不同的
阅读更多2024-09-30
脚本自动化创建AWS EC2实例+安装ElasticSearch和Kibana+集成OpenTelemetry监控
所有服务器部署流程使用同一个部署脚本来保证部署流程标准化每个服务器的server.properties和user-data.txt不一样，每次部署只需要提供这两个文件即可对于部署之后的验证也可写成脚本
阅读更多2024-09-30
JAVA并发编程高级——JDK 新增的原子操作类 LongAdder
既然 AtomicLong的性能瓶颈是由于过多线程同时去竞争一个变量的更新而产生的，那么如果把一个变量分解为多个变量，让同样多的线程去竞争多个资源,是不是就解决了性能问题?使用AtomicLong 时
阅读更多2024-09-30
2024前端技术发展概况
好的错误处理和调试工具，提升开发效率和质量。可访问性和性能优化变得越来越重要，开发者需注重网站加载速度、响应性以及在不同设备和浏览器上的兼容性，采用懒加载、缓存优化等技术，并关注可访问性标准。
阅读更多2024-09-30
【数据库】在 Java 中使用 MongoDB 进行数据聚合
MongoDB 的聚合框架允许对数据进行复杂的处理和计算，这是分析和报告数据的强大工具。本文将介绍如何在 Java 中使用 MongoDB 进行数据聚合。
阅读更多2024-09-30
普通二叉搜索树的模拟实现【C++】
二叉搜索树又称二叉排序树，是具有以下性质的二叉树:若它的左子树不为空，则左子树上所有节点的值都小于根节点的值若它的右子树不为空，则右子树上所有节点的值都大于根节点的值它的左右子树也分别为二叉搜索树。
阅读更多2024-09-30

实用SQL小总结

WHERE 条件 column 为纯英文字符 或 不包含任何字符

WHERE 条件 column 为纯数字 或 不包含任何数字

PARTITION BY 的简单使用

json格式字符串处理相关函数

IS JSON

JSON_VALUE

JSON_QUERY

JSON_TABLE

JSON_EXISTS

JSON索引

相关文章

WHERE 条件 column 为纯英文字符或不包含任何字符

WHERE 条件 column 为纯数字或不包含任何数字