自己推理出来的 SQL逻辑处理 判断分情况执行不同的SQL 值得收藏
SELECT DECODE(STAT,'E',(SELECT 'OK' FROM DUAL),'F',(SELECT 'NO' FROM DUAL ))
FROM(
SELECT CASE WHEN AA-BB > 0 THEN
'E'
ELSE
'F'
END
STAT
from( SELECT X.A AA , F.B BB FROM (SELECT (1-2) A FROM DUAL) X,(SELECT (4-2) B FROM DUAL) F ) );
-----------------这个SQL虽然简单 但是有绕 得收藏
SELECT
CASE WHEN STATUS8 = 'N' THEN 'N' ELSE 'Y' END STATUS,
CASE WHEN STATUS8 = 'N' THEN '报错吧' END MESS
from(
SELECT DECODE(STAT,'E',
(
SELECT DECODE
(
STAT1,'H',
(SELECT
CASE WHEN HKJSSJ-GDSJ > 0 THEN 'N' --表示要考虑烘烤工序
ELSE 'Y' --表示不需要考虑X
END STATUS2
FROM (
SELECT ROUND(TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') - TO_DATE(K.HKGZSJ,'YYYY-MM-DD hh24:mi:ss')) * 24 ) HKJSSJ--F.GDSJ
FROM( SELECT TO_CHAR(MAX(PARTITION_DATE),'YYYY-MM-DD hh24:mi:ss') HKGZSJ FROM SFC_STEP
WHERE SPLIT(OPERATION_BO,2) = 'SBAK1' AND SFC_ROUTER_BO LIKE '%STB130624000021%') K ) ),
------H
'J',(SELECT 'Y' FROM DUAL )
) STATUS1 --J状态 J表示不需要考虑烘烤查询
FROM( ---- 考虑SFC是否经过烘烤
SELECT CASE WHEN NUMCS > 0 THEN
'H' -- E状态 H表示考虑SFC过烘烤查询
ELSE
'J' -- E状态 J表示不需要考虑烘烤查询
END STAT1 FROM (SELECT COUNT(*) NUMCS FROM SFC_STEP
WHERE SPLIT(OPERATION_BO,2) = 'SBAK1' AND SFC_ROUTER_BO LIKE '%STB130624000021%'))
) -------------------- 上面考虑E的情况
,'F',(SELECT 'Y' FROM DUAL ) ) -- F 不用考虑
STATUS8
FROM( ---第二层from 取出 当前时间-刷红胶的时间得到小时 与工单下面的维护时间比较 E时考虑烘烤工序 F不用考虑
SELECT CASE WHEN JSSJ-GDSJ < 0 THEN ---换数字调试
'E' --表示要考虑烘烤工序
ELSE
'F' --表示不需要考虑X
END
STAT,GDSJ --GZSJ 过站时间 gdsj 工单维护时间 JSSJ 计算时间
FROM( ---第一层from 取出 当前时间-刷红胶的时间得到小时 ,工单下面的维护时间
SELECT ROUND(TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') - TO_DATE(X.GZSJ,'YYYY-MM-DD hh24:mi:ss')) * 24 ) JSSJ,F.GDSJ
FROM(SELECT TO_CHAR(PARTITION_DATE,'YYYY-MM-DD hh24:mi:ss') GZSJ FROM SFC_STEP
WHERE SPLIT(OPERATION_BO,2) = 'SPRI1' AND SFC_ROUTER_BO LIKE '%STB130624000030%') X, (SELECT VALUE GDSJ FROM CUSTOM_FIELDS WHERE HANDLE IN(SELECT SHOP_ORDER_BO FROM SFC
WHERE SFC = 'STB130624000030' AND SITE = '1000') AND ATTRIBUTE = 'MAX_VALID_TIME') F ))
);