Appearance
SQL
동시성제어 oracle select for update
http://www.dator.co.kr/hotshin/textyle/236147
WHERE 조건이 여러개일 경우
https://ggmouse.tistory.com/252
sql
-- 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 조회
sql
SELECT *
FROM TBL_TEST
WHERE Score = (CASE WHEN @in_Subject = 'Math' THEN 100 ELSE 0 END)위의 쿼리는 아래와 같이 AND OR 조건으로 바꿔 사용할 수도 있다.
sql
SELECT *
FROM TBL_TEST
WHERE (@in_Subject = 'Math' AND Score = 100)
OR (@in_Subject <> 'Math' AND Score = 0)2.과목이 Math일 경우에만 점수가 100점 그 외의 경우에는 전체 ROW 조회
sql
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 조회
sql
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)LISTAGG
sql
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'
)KEEP (DENSE_RANK)
sql
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';날짜 변환
sql
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';날짜
sql
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_DESCANSI SQL
SQL
조회조건이 겹치는 부분은 제외하기
STATUS <> 'Finish'와 STATUS = 'WatingForProcessing'
WITH에서 사용가능한 부분은 최대한 활용
SQL Developer에서
계획설명 (F10)
SQL 쿼리 속도에 대해서 분석할 수 있음