대학원생이 쉽게 설명해보기

-- (1)
SELECT T1.ANIMAL_ID
    , T1.ANIMAL_TYPE
    , T1.NAME
    FROM ANIMAL_INS T1
        INNER JOIN ANIMAL_OUTS T2
            ON T1.ANIMAL_ID = T2.ANIMAL_ID
                AND T1.SEX_UPON_INTAKE <> T2.SEX_UPON_OUTCOME

-- (2)
SELECT T1.ANIMAL_ID
    , T1.ANIMAL_TYPE
    , T1.NAME
    FROM ANIMAL_OUTS T1
        INNER JOIN ANIMAL_INS T2
            ON T1.ANIMAL_ID = T2.ANIMAL_ID
            
    WHERE SUBSTRING_INDEX(T2.SEX_UPON_INTAKE, ' ', 1) = 'Intact'
        AND SUBSTRING_INDEX(T1.SEX_UPON_OUTCOME, ' ', 1) IN ('Neutered', 'Spayed')
            
    ORDER BY ANIMAL_ID

WITH RECURSIVE A AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT 1 + A.HOUR
      FROM A
      WHERE A.HOUR < 23
     )

SELECT T2.HOUR, COUNT(T1.HOUR) AS COUNT
    FROM (SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR
          FROM ANIMAL_OUTS
          ) T1
        RIGHT JOIN A T2
               ON T1.HOUR = T2.HOUR
    GROUP BY T2.HOUR
    ORDER BY T2.HOUR

SELECT *
    FROM PLACES
    WHERE HOST_ID IN (SELECT DISTINCT HOST_ID
                        FROM PLACES
                        GROUP BY HOST_ID
                        HAVING COUNT(HOST_ID) >= 2)
                        
    ORDER BY ID

(1)

-- ROWNUMBER 사용
SELECT NAME, DATETIME
    FROM (
        SELECT T1.NAME
            , T1.DATETIME
            , ROW_NUMBER() OVER(ORDER BY T1.DATETIME ASC) AS RNK
            FROM ANIMAL_INS T1
                LEFT JOIN ANIMAL_OUTS T2
                    ON T1.ANIMAL_ID = T2.ANIMAL_ID

            WHERE T2.ANIMAL_ID IS NULL
    ) T01
    WHERE RNK <= 3

-- LIMIT 사용
SELECT T1.NAME
    , T1.DATETIME
    FROM ANIMAL_INS T1
        LEFT JOIN ANIMAL_OUTS T2
            ON T1.ANIMAL_ID = T2.ANIMAL_ID
            
    WHERE T2.ANIMAL_ID IS NULL
    ORDER BY DATETIME
    LIMIT 3

(2)

SELECT ANIMAL_ID, NAME
    FROM (
        SELECT T1.*
            , ROW_NUMBER() OVER (ORDER BY T2.DATETIME - T1.DATETIME DESC) AS RNK
            FROM ANIMAL_INS T1
                INNER JOIN ANIMAL_OUTS T2
                    ON T1.ANIMAL_ID = T2.ANIMAL_ID
    ) T01
    
    WHERE RNK <= 2 
    ORDER BY RNK

SELECT DISTINCT T1.ANIMAL_ID
    , T1.NAME
    FROM ANIMAL_INS T1
        INNER JOIN ANIMAL_OUTS T2
            ON T1.ANIMAL_ID = T2.ANIMAL_ID
            
    WHERE T1.DATETIME > T2.DATETIME 
    ORDER BY T1.DATETIME