데이터베이스 질문과 답변

간단한 쿼리문 하나만 질문드리고 싶습니다. 0 2 3,582

by 준일정 [SQL Query] mssql sql oracle mysql [2021.01.05 14:37:26]

22.png (6,675Bytes)

어떻게 짜야 할까요?

by 마농 [2021.01.05 14:50:45]

코드만 체크하면 되나요?
혹시 코드는 같은데 이름은 다른 경우가 있어서 이름까지 체크해야 하는지?

by 메구밍 [2021.01.06 11:28:39]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
WITH
T1 AS (
 SELECT * FROM (
 SELECT '' AS COLUMN_1,'' AS COLUMN_2 FROM DUAL
 UNION ALL SELECT '1001', '수박' FROM DUAL
 UNION ALL SELECT '1002', '고구마' FROM DUAL
 UNION ALL SELECT '1003', '오이' FROM DUAL
 UNION ALL SELECT '1004', '당근' FROM DUAL
 )
WHERE COLUMN_1 IS NOT NULL
)
 
,T2 AS (
 SELECT * FROM (
 SELECT '' AS COLUMN_1,'' AS COLUMN_2 FROM DUAL
 UNION ALL SELECT '1001', '수박' FROM DUAL
 UNION ALL SELECT '1002', '고구마' FROM DUAL
 )
WHERE COLUMN_1 IS NOT NULL
)
 
SELECT
        COLUMN_1
        ,COLUMN_2
FROM
        (
        SELECT
                T1.COLUMN_1
                ,T1.COLUMN_2
                ,T2.COLUMN_2 AS EXISTS_YN
        FROM T1
        LEFT JOIN T2 ON T2.COLUMN_1 = T1.COLUMN_1
        )
WHERE EXISTS_YN IS NULL;
 
 
WITH
T1 AS (
 SELECT * FROM (
 SELECT '' AS COLUMN_1,'' AS COLUMN_2 FROM DUAL
 UNION ALL SELECT '1001', '수박' FROM DUAL
 UNION ALL SELECT '1002', '고구마' FROM DUAL
 UNION ALL SELECT '1003', '오이' FROM DUAL
 UNION ALL SELECT '1004', '당근' FROM DUAL
 )
WHERE COLUMN_1 IS NOT NULL
)
 
,T2 AS (
 SELECT * FROM (
 SELECT '' AS COLUMN_1,'' AS COLUMN_2 FROM DUAL
 UNION ALL SELECT '1001', '수박' FROM DUAL
 UNION ALL SELECT '1002', '고구마' FROM DUAL
 )
WHERE COLUMN_1 IS NOT NULL
)
 
SELECT
        *
FROM
        T1
WHERE
        NOT EXISTS(SELECT 'A' FROM T2 WHERE T2.COLUMN_1 = T1.COLUMN_1);
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입