데이터베이스 질문과 답변
이런 쿼리는 어떻게 작성해야 할 지 도저히 알 수 없어 이렇게 또 질문을 드립니다.
해당 테이블은 아래와 같습니다.
| Col_A | Col_B | Col_C | Col_D |
| R1 | A | 10 | 2024-01-01 09:00:00 |
| R1 | B | 10 | 2024-01-01 10:00:00 |
| R1 | C | 10 | 2024-01-01 11:00:00 |
| R2 | A | 4 | 2024-01-02 09:00:00 |
| R2 | B | 4 | 2024-01-02 10:00:00 |
| W1 | A | 20 | 2024-01-03 09:00:00 |
| W1 | B | 20 | 2024-01-03 10:00:00 |
| W1 | C | 20 | 2024-01-03 11:00:00 |
| W2 | A | 30 | 2024-01-04 09:00:00 |
| W2 | B | 20 | 2024-01-04 10:00:00 |
| W2 | C | 20 | 2024-01-04 11:00:00 |
| W3 | A | 50 | 2024-01-05 09:00:00 |
| W3 | B | 50 | 2024-01-05 10:00:00 |
Col_A 항목에 대한 값을 조회하고 싶은데,
만약 Col_B 에 'C'가 있다면, 조회 결과에서 제외하고 싶습니다.
원하는 결과는 아래와 같습니다.
| Col_A | Col_B | Col_C | Col_D |
| R2 | B | 4 | 2024-01-02 10:00:00 |
| W3 | B | 50 | 2024-01-05 10:00:00 |
Col_A 에 R1, W1, W2 는 Col_B에 C를 포함하고 있지 않습니다.
이런 sql문은 어떻게 작성하면 될까요?
도움을 요청드립니다.
감사합니다.
by 꿈나무
[2024.04.22 12:04:28]
WHERE절에 NOT EXISTS 사용 어떤가요??
--예시--
FROM 테이블 M
WHERE NOT EXISTS(SELECT 1
FROM 테이블 S
WHERE S.COL_A = M.COL_A
AND S.COL_B = 'C' )
모바일이라 들여쓰기가 엉망입니다.
죄송합니다(_ _)
by 신히
[2024.04.22 14:46:18]
간단하게 NOT IN 을 쓰셔도 됩니다.
SELECT DISTINCT Col_A
FROM TBL
WHERE Col_A NOT IN (SELECT Col_A FROM TBL WHERE Col_B = 'C')
;
by 대궁이
[2024.04.22 16:03:05]
원하는 결과값이
말씀하신 기준이라면
| R2 | A |
| R2 | B |
| W3 | A |
| W3 | B |
이렇게 나와야 하는데요 추가 조건이 있을듯 합니다. COL_D 가 최근기준이라던지 ~
말씀하신 기준이 애매해서 한번 참고해서 보시기 바랍니다.
WITH TBL AS
(
SELECT 'R1' COL_A, 'A' AS COL_B,10 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'R1' COL_A, 'B' AS COL_B,10 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'R1' COL_A, 'C' AS COL_B,10 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'R2' COL_A, 'A' AS COL_B,4 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'R2' COL_A, 'B' AS COL_B,4 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W1' COL_A, 'A' AS COL_B,20 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W1' COL_A, 'B' AS COL_B,20 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W1' COL_A, 'C' AS COL_B,20 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W2' COL_A, 'A' AS COL_B,30 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W2' COL_A, 'B' AS COL_B,20 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W2' COL_A, 'C' AS COL_B,20 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W3' COL_A, 'A' AS COL_B,50 AS COL_C, ' ' AS COL_D FROM DUAL UNION ALL
SELECT 'W3' COL_A, 'B' AS COL_B,50 AS COL_C, ' ' AS COL_D FROM DUAL
)
SELECT *
FROM TBL
WHERE COL_A NOT IN ('R1','W1','W2')
AND COL_B != 'C'
;
by 마농
[2024.04.23 01:28:34]
WITH t AS
(
SELECT 'R1' a, 'A' b, 10 c, '2024-01-01 09:00:00' d
UNION ALL SELECT 'R1', 'B', 10, '2024-01-01 10:00:00'
UNION ALL SELECT 'R1', 'C', 10, '2024-01-01 11:00:00'
UNION ALL SELECT 'R2', 'A', 4, '2024-01-02 09:00:00'
UNION ALL SELECT 'R2', 'B', 4, '2024-01-02 10:00:00'
UNION ALL SELECT 'W1', 'A', 20, '2024-01-03 09:00:00'
UNION ALL SELECT 'W1', 'B', 20, '2024-01-03 10:00:00'
UNION ALL SELECT 'W1', 'C', 20, '2024-01-03 11:00:00'
UNION ALL SELECT 'W2', 'A', 30, '2024-01-04 09:00:00'
UNION ALL SELECT 'W2', 'B', 20, '2024-01-04 10:00:00'
UNION ALL SELECT 'W2', 'C', 20, '2024-01-04 11:00:00'
UNION ALL SELECT 'W3', 'A', 50, '2024-01-05 09:00:00'
UNION ALL SELECT 'W3', 'B', 50, '2024-01-05 10:00:00'
)
SELECT *
FROM (SELECT a, b, c, d
, COUNT(CASE b WHEN 'C' THEN 1 END) OVER(PARTITION BY a) cnt
, ROW_NUMBER() OVER(PARTITION BY a ORDER BY d DESC) rn
FROM t
) a
WHERE cnt = 0
AND rn = 1
;
by tosswin
[2024.04.26 09:30:39]
감사합니다. 마농님.
이번에도 많은 도움 받았습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.