목록sql (25)
rose_brown
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/301651 2. 코드WITH RECURSIVE GEN AS( SELECT ID, PARENT_ID, 1 AS GENERATION FROM ECOLI_DATA WHERE PARENT_ID IS NULL UNION ALL SELECT E.ID, E.PARENT_ID, G.GENERATION + 1 FROM ECOLI_DATA E JOIN GEN G ON E.PARENT_ID = G.ID)SELECT COUNT(*) AS COUNT, GENERATIONFROM GENWHERE ID NOT IN ( SELECT DISTINCT PARENT_I..
[프로그래머스] SQL - 업그레이드 된 아이템 구하기
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/273711 2. 코드SELECT i.ITEM_ID, i.ITEM_NAME, i.RARITYFROM ITEM_INFO iJOIN ITEM_TREE t ON i.ITEM_ID = t.ITEM_IDJOIN ITEM_INFO parent ON t.PARENT_ITEM_ID = parent.ITEM_IDWHERE parent.RARITY = 'RARE'ORDER BY i.ITEM_ID DESC; 참고SELECT *FROM ITEM_INFO iJOIN ITEM_TREE t ON i.ITEM_ID = t.ITEM_IDJOIN ITEM_INFO parent ON t.PARENT_ITEM_..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/293261 2. 코드실패 코드SELECT i.ID, n.FISH_NAME, MAX(i.LENGTH) AS LENGTHFROM FISH_INFO iJOIN FISH_NAME_INFO n ON i.FISH_TYPE = n.FISH_TYPEWHERE i.LENGTH >= 10GROUP BY i.ID, n.FISH_NAMEORDER BY i.ID;성공 코드SELECT i.ID, n.FISH_NAME, i.LENGTHFROM FISH_INFO iJOIN FISH_NAME_INFO n ON i.FISH_TYPE = n.FISH_TYPEJOIN ( SELECT FISH_TYPE, MAX(LENGTH..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/2737122. 코드SELECT i.ITEM_ID, i.ITEM_NAME, i.RARITYFROM ITEM_INFO iLEFT JOIN ITEM_TREE t ON i.ITEM_ID = t.PARENT_ITEM_IDWHERE t.PARENT_ITEM_ID IS NULLORDER BY i.ITEM_ID DESC;3. 다른 코드SELECT ITEM_ID, ITEM_NAME, RARITYFROM ITEM_INFOWHERE ITEM_ID NOT IN (SELECT PARENT_ITEM_ID FROM ITEM_TREE WHERE..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/298519 2. 코드반만 성공 코드SELECT COUNT(*) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPEFROM FISH_INFOGROUP BY FISH_TYPEHAVING AVG(LENGTH) >= 33ORDER BY FISH_TYPE;완전 성공 코드SELECT COUNT(*) AS FISH_COUNT, MAX(LENGTH) AS MAX_LENGTH, FISH_TYPEFROM FISH_INFOGROUP BY FISH_TYPEHAVING AVG(COALESCE(LENGTH, 10)) >= 33ORDER..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/299305 2. 코드코드 1SELECT p.ID, (SELECT COUNT(*) FROM ECOLI_DATA c WHERE p.ID = c.PARENT_ID) AS CHILD_COUNTFROM ECOLI_DATA pORDER BY p.ID;코드 2SELECT p.ID, COUNT(c.ID) AS CHILD_COUNTFROM ECOLI_DATA pLEFT JOIN ECOLI_DATA c ON p.ID = c.PARENT_IDGROUP BY p.IDORDER BY p.ID;
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/774872. 코드오답코드SELECT * FROM PLACESGROUP BY HOST_IDHAVING COUNT(HOST_ID) >= 2ORDER BY ID;HOST_ID가 2건 이상인 헤비 유저들의 전체 리스트가 나와야 하는데 전체 리스트가 출력정답코드SELECT * FROM PLACES WHERE HOST_ID IN (SELECT HOST_ID FROM PLACES GROUP BY HOST_ID HAVING COUNT(HOST_ID) >= 2) ORDER BY ID3. 다른 코드SELECT * FROM PLACES pWHERE ..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/1646712. 코드SELECT CONCAT("/home/grep/src/",b.BOARD_ID,"/",f.FILE_ID,f.FILE_NAME,f.FILE_EXT) AS FILE_PATHFROM USED_GOODS_BOARD bJOIN USED_GOODS_FILE f USING(BOARD_ID)WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)ORDER BY b.VIEWS DESC, f.FILE_ID DESC;3. 다른 코드SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, F..
1. 문제https://school.programmers.co.kr/learn/courses/30/lessons/1322042. 코드SELECT a.APNT_NO, p.PT_NAME, p.PT_NO, d.MCDP_CD, d.DR_NAME, a.APNT_YMDFROM DOCTOR dJOIN APPOINTMENT a ON a.MDDR_ID = d.DR_IDJOIN PATIENT p ON p.PT_NO = a.PT_NOWHERE a.APNT_YMD LIKE "2022-04-13%" AND a.APNT_CNCL_YMD IS NULL AND a.MCDP_CD="CS"ORDER BY a.APNT_YMD;3. 다른 코드SELECT a.APNT_NO, p.PT_NAME, p.PT_NO, d.MCDP_CD, d.DR_N..