SQL 解析 with as
sql的运行次序https://i-blog.csdnimg.cn/direct/00ddd50382f84ff1b78f8fe09ffe3482.png
<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_YEAR
FROM DUAL
CONNECT BY LEVEL <= 8
),
-- 原始数据解析
split_data AS (
SELECT
SBBH,
EXTRACT(YEAR FROM BCJYRQ) AS INSPECTION_YEAR,
(SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))
FROM DUAL
CONNECT 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 DUAL
CONNECT 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 DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(NVL(TT, '0/0'), '/') + 1
) AS TT_MIN
FROM sbjcpg_hysb_jyxx
WHERE
SBBH = #{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,-- 无数据时为 NULL
MIN(sd.FT2_MIN) AS FT2,
MIN(sd.TT_MIN) AS TT
FROM year_range yr
LEFT JOIN split_data sd
ON yr.INSPECTION_YEAR = sd.INSPECTION_YEAR
GROUP BY yr.INSPECTION_YEAR
ORDER BY yr.INSPECTION_YEAR
]]>
</select> with as
用时创建临时表,让代码看起来更轻便，节约性能
WITH
cte1 AS (SELECT ... FROM ...),
cte2 AS (SELECT ... FROM cte1 WHERE ...)
SELECT * FROM cte2; https://i-blog.csdnimg.cn/direct/5ba3b995f9274f72807fd990836b8de9.png
EXTRACT
是用来提取
https://i-blog.csdnimg.cn/direct/943535433fcc4282b23e3204f05eeba1.png dual
DUAL 表是 Oracle 中一个简单但强盛的工具，主要用于实行与数据表无关的快速计算、函数调用或测试。明白它的用途可以显著提升 SQL 编写的效率和灵活性。https://i-blog.csdnimg.cn/direct/43d32684ad554bfb9219d875ace172f4.png
sysdate
https://i-blog.csdnimg.cn/direct/564c5c0e53744d7ea41d69175ae96724.png https://i-blog.csdnimg.cn/direct/fe6a7113060f48c8b35f379ec42f19cb.png
level
https://i-blog.csdnimg.cn/direct/aa8e4c268a774c9c851a76393aa4ddc9.png https://i-blog.csdnimg.cn/direct/56a30230ea2047ecbaca8262e5afe068.png
SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT1, '0/0'), '/') + 1 https://i-blog.csdnimg.cn/direct/dcfd80647e844f6d98c89eab964afa46.pnghttps://i-blog.csdnimg.cn/direct/a5ca8f71b02e401bb05b66df2f70a06a.png
Oracle中的日期存储
参考链接
使用Oracle SQL查询提取日期中的年份高效本领详解 - 云原生实践
ORACLE——EXTRACT() 截取日期时间的函数使用 - 九零大叔芭蕉 - 博客园
https://i-blog.csdnimg.cn/direct/55a2e77d99d34e3b9024d2f07c07fe15.png 蓝色区域的主要逻辑
核心部分
https://i-blog.csdnimg.cn/direct/b940fa32ceb94d4a956f584bfe084c59.png
主查询结构
SELECT
SBBH,-- 设备编号
EXTRACT(YEAR FROM BCJYRQ) AS INSPECTION_YEAR,-- 检测年份
(子查询) AS FT1_MIN-- 计算 FT1 字段的最小值
FROM sbjcpg_hysb_jyxx
WHERE
SBBH = #{sbbh}-- 筛选指定设备编号
AND EXTRACT(YEAR FROM BCJYRQ) BETWEEN
EXTRACT(YEAR FROM SYSDATE) - 8-- 起始年份（当前年-8）
AND EXTRACT(YEAR FROM SYSDATE) - 1-- 结束年份（当前年-1） https://i-blog.csdnimg.cn/direct/9b66cfd6f22f49b1812e7e2d77ea2222.png
拆解字符串并计算最小值
(
SELECT MIN(TO_NUMBER(REGEXP_SUBSTR(NVL(FT1, '0/0'), '\d+\.?\d*', 1, LEVEL)))
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(NVL(FT1, '0/0'), '/') + 1
) AS FT1_MIN 这个查询中用到了FT1字段，但是他 只从dual表中查的 ，其实这是
关联子查询
https://i-blog.csdnimg.cn/direct/eadfc02733ba439ea0bece723bebe27d.png
https://i-blog.csdnimg.cn/direct/4304b58411154a9aa18c0ec3bbf0c705.png
https://i-blog.csdnimg.cn/direct/1366b0ceb87949f7b88c6f6826803d5d.png
NVL 函数
https://i-blog.csdnimg.cn/direct/fe8adc150afb4952b2af33907c4dbcf7.png
https://i-blog.csdnimg.cn/direct/6e1a13a784254707818ec9328f4d2236.png Oracle REGEXP_SUBSTR() 函数使用指南Oracle REGEXP_SUBSTR() 是一个内置函数，它从一个给定的源字符串中搜索并返回一个与给定的正则表达式匹配的字符串。https://csdnimg.cn/release/blog_editor_html/release2.3.8/ckeditor/plugins/CsdnLink/icons/icon-default.png?t=P1C7https://www.sjkjc.com/oracle-ref/regexp_substr/https://i-blog.csdnimg.cn/direct/7b228ab35f4f41c7b8b687f834874f30.pnghttps://i-blog.csdnimg.cn/direct/af16aed8c3544271b7a887d5ca772bf5.png
REGEXP_SUBSTR()
https://i-blog.csdnimg.cn/direct/1d03cb1ce5b649b8814e91a8a937813f.png
https://i-blog.csdnimg.cn/direct/2613372fafd24680b29af4bf8866e764.png https://i-blog.csdnimg.cn/direct/1ceb2c4cbd584c89a02df24d114d5654.pnghttps://i-blog.csdnimg.cn/direct/f2d1f328748848569d60531738da5d9c.png
https://i-blog.csdnimg.cn/direct/a07393ffcdf74ed9aa65343479673bc0.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]