当前位置：首页 > news >正文

SQL 解析 with as dual sysdate level

news 2025/4/16 6:45:48

sql的运行顺序

with as

EXTRACT

编辑 dual

sysdate

level

编辑编辑

Oracle中的日期存储

核心部分

拆解字符串并计算最小值

关联子查询

NVL 函数

REGEXP_SUBSTR()

sql的运行顺序

<select id="getTrendList"  parameterType="java.util.HashMap" resultType="java.util.Map"><![CDATA[WITH-- 生成连续年份列表（当前年前8年到前1年）year_range AS (SELECT EXTRACT(YEAR FROM SYSDATE) - 8 + LEVEL - 1 AS INSPECTION_YEARFROM DUALCONNECT BY LEVEL <= 8),-- 原始数据解析split_data AS (SELECTSBBH,EXTRACT(YEAR FROM BCJYRQ) AS INSPECTION_YEAR,(SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))FROM DUALCONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT1, '0/0'), '/') + 1) AS FT1_MIN,(SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT2, '0/0'), '\d+\.?\d*', 1, LEVEL)))FROM DUALCONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT2, '0/0'), '/') + 1) AS FT2_MIN,(SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(TT, '0/0'), '\d+\.?\d*', 1, LEVEL)))FROM DUALCONNECT BY LEVEL <= REGEXP_COUNT(NVL(TT, '0/0'), '/') + 1) AS TT_MINFROM sbjcpg_hysb_jyxxWHERESBBH = #{sbbh}AND EXTRACT(YEAR FROM BCJYRQ) BETWEEN EXTRACT(YEAR FROM SYSDATE) - 8 AND EXTRACT(YEAR FROM SYSDATE) - 1)-- 最终结果（左连接补全年份）SELECT#{sbbh} AS SBBH,  -- 固定设备编号yr.INSPECTION_YEAR AS YEAR,MIN(sd.FT1_MIN) AS FT1,  -- 无数据时为 NULLMIN(sd.FT2_MIN) AS FT2,MIN(sd.TT_MIN) AS TTFROM year_range yrLEFT JOIN split_data sdON yr.INSPECTION_YEAR = sd.INSPECTION_YEARGROUP BY yr.INSPECTION_YEARORDER BY yr.INSPECTION_YEAR]]></select>

with as

用时创建临时表,让代码看起来更简洁，节约性能

WITHcte1 AS (SELECT ... FROM ...),cte2 AS (SELECT ... FROM cte1 WHERE ...)
SELECT * FROM cte2;

EXTRACT

是用来提取

dual

DUAL 表是 Oracle 中一个简单但强大的工具，主要用于执行与数据表无关的快速计算、函数调用或测试。理解它的用途可以显著提升 SQL 编写的效率和灵活性。

sysdate

level

SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))FROM DUALCONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT1, '0/0'), '/') + 1

Oracle中的日期存储

参考链接

使用Oracle SQL查询提取日期中的年份高效技巧详解 - 云原生实践

ORACLE——EXTRACT() 截取日期时间的函数使用 - 九零大叔芭蕉 - 博客园

蓝色区域的主要逻辑

核心部分

主查询结构

SELECTSBBH,  -- 设备编号EXTRACT(YEAR FROM BCJYRQ) AS INSPECTION_YEAR,  -- 检测年份(子查询) AS FT1_MIN  -- 计算 FT1 字段的最小值
FROM sbjcpg_hysb_jyxx
WHERESBBH = #{sbbh}  -- 筛选指定设备编号AND EXTRACT(YEAR FROM BCJYRQ) BETWEEN EXTRACT(YEAR FROM SYSDATE) - 8  -- 起始年份（当前年-8）AND EXTRACT(YEAR FROM SYSDATE) - 1  -- 结束年份（当前年-1）

拆解字符串并计算最小值

(SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))FROM DUALCONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT1, '0/0'), '/') + 1
) AS FT1_MIN

这个查询中用到了FT1字段，但是他只从dual表中查的，其实这是