로메오의 블로그

[SQL] distinct 중복제거 예제 본문

Backend/Oracle & SQL

[SQL] distinct 중복제거 예제

romeoh 2019. 8. 29. 22:26
반응형

[DOCKER] MAC에 DOCKER 설치하기

[ORACLE] DOCKER에 ORACLE 11G 설치하기

[POSTGRESQL] DOCKER에 POSTGRESQL 설치하기

 

중복된 값을 제거하는 distinct 에 대해 알아봅니다.

SQL >
SELECT COMPANY, PRODUCT
FROM (
  SELECT 'Google' COMPANY, 'Android' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Google' COMPANY, 'Chrome' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'Macbook' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPhone' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPod' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT FROM DUAL
)
COMPANY PRODUCT
------- -------
Google	Android
Google	Chrome
Apple	Macbook
Apple	Macbook
Apple	iPhone
Apple	iPod
Samsung Gallexy

7 rows selected.

위와 같은 테이블이 있습니다.

 

COMPANY의 중복된 값을 제거해 보겠습니다.

 

SQL>
SELECT DISTINCT COMPANY
FROM (
  SELECT 'Google' COMPANY, 'Android' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Google' COMPANY, 'Chrome' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'Macbook' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPhone' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPod' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT FROM DUAL
);
COMPANY
-------
Google
Samsung
Apple

3 rows selected.

COMPANY컬럼의 중복되는 row가 제거되고 3개의 고유한 값만 조회합니다.

 

 

이번에는 COMPANY, PRODUCT의 DISTINCT를 조회합니다.

SQL >
SELECT DISTINCT COMPANY, PRODUCT
FROM (
  SELECT 'Google' COMPANY, 'Android' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Google' COMPANY, 'Chrome' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'Macbook' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPhone' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Apple' COMPANY, 'iPod' PRODUCT FROM DUAL
  UNION ALL
  SELECT 'Samsung' COMPANY, 'Gallexy' PRODUCT FROM DUAL
);
COMPANY PRODUCT
------- -------
Apple	Macbook
Apple	iPod
Apple	iPhone
Samsung Gallexy
Google	Android
Google	Chrome

6 rows selected.

COMPANY와 PRODUCT를 모두 반영하여 중복된 row가 제거되고

고유한 값을 조회합니다.

반응형
Comments