카테고리 없음

Exist (Select 1~

비비이잉 2023. 3. 20. 16:16
반응형

1.

SELECT A.ID, A.REVIEW_ID, A.CLASS, A.DIE_C_IDX, A.DIE_R_IDX, B.CLASS AS ADC_CLASS
FROM TB_POINT A 
LEFT JOIN TB_ADC_RESULT B
ON A.CREATE_TIME = B.CREATE_TIME AND A.ID = B.POINT_ID
AND B.MODEL_ID =5
AND B.CLASS IS NOT NULL
WHERE A.REVIEW_ID = '70489' AND A.STATUS = 0 
AND EXISTS (
    SELECT 1 
    FROM TB_POINT C
    LEFT JOIN TB_ADC_RESULT D
    ON C.CREATE_TIME = D.CREATE_TIME AND C.ID = D.POINT_ID
    AND D.MODEL_ID = 5
    AND D.CLASS IS NOT NULL
    WHERE C.REVIEW_ID = '70489' AND C.STATUS = 0 
    AND C.DIE_C_IDX = A.DIE_C_IDX 
    AND C.DIE_R_IDX = A.DIE_R_IDX
    AND D.CLASS IS NOT NULL
);

 

2. 

SELECT A.ID, A.REVIEW_ID, A.CLASS, A.DIE_C_IDX, A.DIE_R_IDX, B.CLASS AS ADC_CLASS
FROM TB_POINT A 
LEFT JOIN TB_ADC_RESULT B
ON A.CREATE_TIME = B.CREATE_TIME AND A.ID = B.POINT_ID
AND B.MODEL_ID =5
AND B.CLASS IS NOT NULL
WHERE A.REVIEW_ID = '70489' AND A.STATUS = 0 
AND (A.DIE_C_IDX, A.DIE_R_IDX) IN 
    (SELECT TT.DIE_C_IDX, TT.DIE_R_IDX FROM 
    (SELECT T.DIE_C_IDX, T.DIE_R_IDX, T.ADC_CLASS FROM 
    (SELECT A.ID, A.REVIEW_ID, A.CLASS, A.DIE_C_IDX, A.DIE_R_IDX, B.CLASS AS ADC_CLASS
    FROM TB_POINT A
    LEFT JOIN TB_ADC_RESULT B
    ON A.CREATE_TIME = B.CREATE_TIME AND A.ID = B.POINT_ID 
    AND B.MODEL_ID = 5
    AND B.CLASS IS NOT NULL
    WHERE A.REVIEW_ID = '70489' AND A.STATUS = 0)T
    GROUP BY DIE_C_IDX, DIE_R_IDX 
    HAVING T.ADC_CLASS IS NOT NULL) TT);

 

AND EXIST (SELECT 1~ 

쿼리가 하나의 행을 가져와서 해당 서브쿼리에서 존재하는지 여부를 판단하기 때문에 더 간단하게 나타낼 수 있음 

반응형