동시성제어 oracel select for update http://www.dator.co.kr/hotshin/textyle/236147

WHERE 조건이 여러개일 경우 https://ggmouse.tistory.com/252

-- AND (ICG.VERAN NOT IN (SELECT GENE_CD FROM T_CD_GENE_DATA WHERE GENE_DV = 'DEFECT_EXCEPT01') AND ICG.GRUND = '9P38' ) -- AND (CASE WHEN ICG.VERAN IN (SELECT GENE_CD FROM T_CD_GENE_DATA WHERE GENE_DV = 'DEFECT_EXCEPT01') THEN ICG.GRUND ELSE '' END) = '9P38' -- AND (CASE WHEN ICG.VERAN IN (SELECT GENE_CD FROM T_CD_GENE_DATA WHERE GENE_DV = 'DEFECT_EXCEPT02') THEN ICG.LIFNR ELSE '' END) = (CASE WHEN ICG.VERAN IN (SELECT GENE_CD FROM T_CD_GENE_DATA WHERE GENE_DV = 'DEFECT_EXCEPT02') THEN ICG.LIFNR ELSE '' END) -- AND (ICG.VERAN NOT IN (SELECT GENE_CD FROM T_CD_GENE_DATA WHERE GENE_DV = 'DEFECT_EXCEPT02') AND ICG.LIFNR IS NULL )

1.과목이 Math일 경우에만 점수가 100점 그 외의 경우에는 0점인 ROW 조회

SELECT * FROM TBL_TEST WHERE Score = (CASE WHEN @in_Subject = 'Math' THEN 100 ELSE 0 END)

위의 쿼리는 아래와 같이 AND OR 조건으로 바꿔 사용할 수도 있다. SELECT * FROM TBL_TEST WHERE (@in_Subject = 'Math' AND Score = 100) OR (@in_Subject <> 'Math' AND Score = 0)

2.과목이 Math일 경우에만 점수가 100점 그 외의 경우에는 전체 ROW 조회 SELECT * FROM TBL_TEST WHERE (CASE WHEN @in_Subject = 'Math' THEN Score ELSE '' END) = (CASE WHEN @in_Subject = 'Math' THEN 100 ELSE '' END)

3.과목이 Math일 경우 점수가 100점 English일 경우에 점수가 50점인 ROW 조회 SELECT * FROM TBL_TEST WHERE (CASE WHEN @in_Subject IN ('Math','English') THEN Score ELSE '' END) = (CASE WHEN @in_Subject = 'Math' THEN 100 WHEN @in_Subject = 'English' THEN 50 ELSE '' END)

#

https://m.blog.naver.com/PostView.nhn?blogId=ohhyun93&logNo=221076118831&proxyReferer=https%3A%2F%2Fwww.google.com%2F

SELECT LISTAGG(REASON_DESC,',') WITHIN GROUP(ORDER BY REASON_DESC) AS REASON_DESC FROM ( SELECT TO_CHAR(TO_DATE(SUBSTR(REPLACE(X1.START_DATE_TIME, ' ', ''), 1, 14), 'YYYY-MM-DD HH24:MI:SS'),'MM-DD HH24:MM') ||'~' || TO_CHAR(NVL(TO_DATE(SUBSTR(REPLACE(X1.END_DATE_TIME, ' ', ''), 1, 14), 'YYYY-MM-DD HH24:MI:SS'), SYSDATE),'MM-DD HH24:MM') ||'(' ||X1.REASON_DESC || ')' REASON_DESC FROM EQP_HISTORY X1 WHERE X1.EQP_ID = '100216' AND X1.START_DATE_TIME >= TO_CHAR(SYSTIMESTAMP-NUMTODSINTERVAL(30,'DAY'),'YYYYMMDD') || ' 000000000' AND X1.STATE = 'Down' )

CASE WHEN SELECT MAX(X1.REASON_DESC) KEEP (DENSE_RANK LAST ORDER BY X1.START_DATE_TIME) AS REASON_DESC FROM EQP_HISTORY X1 WHERE X1.EQP_ID = '100216' AND X1.START_DATE_TIME > EH.START_DATE_TIME (SELECT MAX(EH.REASON_DESC) KEEP (DENSE_RANK FIRST ORDER BY EH.START_DATE_TIME) FROM EQP_HISTORY EH WHERE 1 =1 AND EH.START_DATE_TIME >= TO_CHAR(SYSTIMESTAMP-NUMTODSINTERVAL(2,'DAY'),'YYYYMMDD') || ' 000000000' AND EH.EQP_ID = T1.EQP_ID AND EH.START_DATE_TIME >= TO_CHAR(TO_DATE(T1.START_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD HH24MISS')) IS NULL SELECT MAX(REASON_DESC) KEEP (DENSE_RANK LAST ORDER BY START_DATE_TIME) AS REASON_DESC FROM EQP_HISTORY WHERE EQP_ID = '100216';

#

TO_CHAR(TO_DATE(SUBSTR(REPLACE(MAX(X1.START_DATE_TIME), ' ', ''), 1, 14), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') SELECT TO_CHAR(TO_DATE(SUBSTR(REPLACE(MAX(X1.START_DATE_TIME) KEEP (DENSE_RANK FIRST ORDER BY X1.START_DATE_TIME DESC), ' ', ''), 1, 14), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS REASON_DESC FROM EQP_HISTORY X1 WHERE X1.EQP_ID = '100218' SELECT * FROM EQP_HISTORY WHERE EQP_ID = '100218' AND START_DATE_TIME >= TO_CHAR(SYSTIMESTAMP-NUMTODSINTERVAL(2,'DAY'),'YYYYMMDD') || ' 000000000';

#

날짜 TO_CHAR(TO_DATE(T1.START_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD HH24MISS') AS START_TIME, (SELECT MAX(CYCLE_TIME_CUR) FROM EQP_CONT_HIST ECH WHERE ECH.EQP_ID = T1.EQP_ID AND ECH.PART_NUMBER = T1.PART_NUMBER) CYCLE_TIME_CUR ,(SELECT MIN(X1.START_DATE_TIME) FROM EQP_HISTORY X1 WHERE AND X1.START_DATE_TIME >= TO_CHAR(SYSTIMESTAMP-NUMTODSINTERVAL(2,'DAY'),'YYYYMMDD') || ' 000000000' AND X1.EQP_ID = T1.EQP_ID AND TO_CHAR(TO_DATE(SUBSTR(REPLACE(X1.START_DATE_TIME, ' ', ''), 1, 14), 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDDHH24MISS') >= TO_CHAR(TO_DATE(T1.START_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDDHH24MISS') ) REASON_DESC

#ansi sql

#SQL

조회조건이 겹치는 부분은 제외하기 STATUS <> 'Finish'와 STATUS = 'WatingForProcessing'

WITH에서 사용가능한 부분은 최대한 활용

#SQL Developer에서 ##계획설명 (F10) SQL 쿼리 속도에 대해서 분석할 수 있음

#쿼리만들기 집합적사고방식

http://poorsql.com/

http://www.dpriver.com/pp/sqlformat.htm