目錄

 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

用