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

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

-- GROUP BY 방법
SELECT CART_ID
    FROM (
        SELECT DISTINCT CART_ID, NAME
            FROM CART_PRODUCTS
            WHERE NAME IN ('Milk', 'Yogurt')
        ) T1
    GROUP BY CART_ID
    HAVING COUNT(1) >= 2
    ORDER BY CART_ID
    
-- SUBQUERY 방법
SELECT DISTINCT CART_ID
    FROM CART_PRODUCTS
    WHERE NAME IN ('Milk')
        AND CART_ID IN (SELECT CART_ID 
                            FROM CART_PRODUCTS
                            WHERE NAME IN ('Yogurt'))