|
||
![]() |
![]() ![]()
WITH WT_LOAN_CNT AS
( SELECT AA , COUNT(CC) AS BB FROM LOAN WHERE CC ='À¯' GROUP BY AA ), WT_EMP AS ( SELECT AA , BB , CC , DD FROM EMP )SELECT WT_EMP.* , CASE WHEN WT_LOAN_CNT.BB IS NULL THEN '¹«' ELSE 'À¯' END AS BB_YN FROM WT_EMP LEFT OUTER JOIN WT_LOAN_CNT ON WT_LOAN_CNT.AA = WT_EMP.BB ----- LOAN Å×À̺í CREATE TABLE LOAN ( AA VARCHAR2(100 BYTE), BB VARCHAR2(100 BYTE), CC VARCHAR2(100 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) REM INSERTING into LOAN SET DEFINE OFF; Insert into LOAN (AA,BB,CC) values ('È«±æµ¿','¿ì¸®ÀºÇà','À¯'); Insert into LOAN (AA,BB,CC) values ('È«±æµ¿','ÇϳªÀºÇà','¹«'); Insert into LOAN (AA,BB,CC) values ('È«±æµ¿','³óÇù','¹«'); Insert into LOAN (AA,BB,CC) values ('³ª¾ß³ª','¿ì¸®ÀºÇà','¹«'); Insert into LOAN (AA,BB,CC) values ('³ª¾ß³ª','ÇϳªÀºÇà','¹«'); Insert into LOAN (AA,BB,CC) values ('³ª¾ß³ª','³óÇù','¹«'); Insert into LOAN (AA,BB,CC) values ('ÀÌÇϳª','¿ì¸®ÀºÇà','À¯'); Insert into LOAN (AA,BB,CC) values ('ÀÌÇϳª','ÇϳªÀºÇà','À¯'); Insert into LOAN (AA,BB,CC) values ('ÀÌÇϳª','³óÇù','À¯'); Insert into LOAN (AA,BB,CC) values ('±èö¼ö','¿ì¸®ÀºÇà','¹«'); Insert into LOAN (AA,BB,CC) values ('±èö¼ö','ÇϳªÀºÇà','À¯'); Insert into LOAN (AA,BB,CC) values ('±èö¼ö','³óÇù','À¯'); ---EMP Å×À̺í CREATE TABLE EMP ( AA VARCHAR2(100 BYTE), BB VARCHAR2(100 BYTE), CC VARCHAR2(100 BYTE), DD VARCHAR2(100 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) REM INSERTING into EMP SET DEFINE OFF; Insert into EMP (AA,BB,CC,DD) values ('¼¿ï','È«±æµ¿','³²','29'); Insert into EMP (AA,BB,CC,DD) values ('¼¿ï','³ª¾ß³ª','³²','30'); Insert into EMP (AA,BB,CC,DD) values ('¼¿ï','ÀÌÇϳª','³²','29'); Insert into EMP (AA,BB,CC,DD) values ('ºÎ»ê','±èö¼ö','³²','31'); ¸ÕÀú ´ëÃâÀÌ ÀÖ´ÂÁö ¾ø´ÂÁö Ä«¿îÆ® ÇØ¿À°í WITH Àý·Î ¹¾îÁÖ°í EMP ȸ¿ø ¸¶½ºÅÍ Å×À̺íÀ» WITH Àý·Î ¹°í EMP¿Í LOAN Å×À̺íÀ» LEFT OUTER JOIN À¸·Î ON Á¶°ÇÀ¸·Î À̸§À» ÇÏ¸é ŠÃÀÌ ³ªÁ® °Å±â¿¡ NULLÀΰÍÀº ´ëÃâÀÌ ¹« ¶ó´Â ¶æÀ̱⠶§¹®¿¡ CASE WHEN THEN À¸·Î NULL ÀΰÍÀº ¹« ·Î ÇØÁÖ°í ÀÖÀ¸¸é À¯·Î ÇØÁÖ°í Âü ½±Á®
2017-10-20 *
![]()
|
|
|
![]() |
![]() | ¡â ÀÌÀü±Û¡ä ´ÙÀ½±Û | -¸ñ·Ïº¸±â | ![]() |
from emp t1
left outer join loan t2
on t1.À̸§ = t2.À̸§
GROUP BY t1.Áö¿ª, t1. À̸§, t1.¼ºº°, t1.³ªÀÌ