본문 바로가기

SQL56

[SQL] 프로그래머스 [Level-3] 오랜기간보호한동물(2) SELECT A.ANIMAL_ID , A.NAME FROM ANIMAL_OUTS A INNER JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID ORDER BY DATEDIFF(A.DATETIME, B.DATETIME) DESC LIMIT 2 SELECT T.ANIMAL_ID , T.NAME FROM ( SELECT A.ANIMAL_ID , A.NAME FROM ANIMAL_OUTS A INNER JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID ORDER BY TO_DATE(A.DATETIME) - TO_DATE(B.DATETIME) DESC ) T WHERE ROWNUM 2022. 12. 1.
[SQL] 프로그래머스 [Level-4] 보호소에서 중성화한 동물 SELECT A.ANIMAL_ID , A.ANIMAL_TYPE , A.NAME FROM ANIMAL_OUTS A INNER JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID AND A.SEX_UPON_OUTCOME != B.SEX_UPON_INTAKE ORDER BY A.ANIMAL_ID 프로그래머스 보호소에서 중성화한 동물 SQL 2022. 12. 1.
[SQL] 프로그래머스 [Level-2] DATETIME에서 DATE로 형 변환 SELECT ANIMAL_ID , NAME , DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜' # %Y %m %d %H %i %s FROM ANIMAL_INS ORDER BY ANIMAL_ID SELECT ANIMAL_ID , NAME , TO_CHAR(DATETIME, 'YYYY-MM-DD') AS 날짜 -- YYYY MM DD HH24 MI SS FROM ANIMAL_INS ORDER BY ANIMAL_ID 프로그래머스 DATETIME에서 DATE로 형 변환 SQL 2022. 12. 1.
[SQL] 프로그래머스 [Level-3] 오랜기간보호한동물(1) SELECT NAME , DATETIME FROM ANIMAL_INS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS) ORDER BY DATETIME LIMIT 3 SELECT T.NAME , T.DATETIME FROM ( SELECT NAME , DATETIME FROM ANIMAL_INS WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS) ORDER BY DATETIME ) T WHERE ROWNUM 2022. 12. 1.
[SQL] 프로그래머스 [Level-4] 입양시각구하기(2) SET @HOUR = -1; SELECT (@HOUR := @HOUR + 1) AS HOUR , (SELECT COUNT(HOUR(DATETIME)) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT FROM ANIMAL_OUTS WHERE @HOUR < 23 SELECT HOUR , COUNT(B.DATETIME) AS COUNT FROM ( SELECT LEVEL-1 AS HOUR FROM DUAL CONNECT BY LEVEL 2022. 12. 1.
[SQL] 프로그래머스 [Level-2] 입양시각구하기(1) SELECT A.HOUR , A.COUNT FROM ( SELECT DATE_FORMAT(DATETIME, '%Y-%m-%d %H:%i:%s') AS TOTAL_DATE , DATE_FORMAT(DATETIME, '%H') AS HOUR , COUNT(SUBSTR(DATETIME, 12, 2)) AS COUNT FROM ANIMAL_OUTS GROUP BY SUBSTR(DATETIME, 12, 2) ) A WHERE A.HOUR BETWEEN 9 AND 20 ORDER BY HOUR SELECT A.HOUR , A.COUNT FROM ( SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR , COUNT(TO_CHAR(DATETIME, 'HH24')) AS COUNT FROM ANIM.. 2022. 12. 1.

티스토리툴바