SQL 종합 (GROUP BY,JOIN,STRING)

 

https://school.programmers.co.kr/learn/courses/30/lessons/164668

프로그래머스

 

SELECT u.USER_ID, u.NICKNAME, SUM(b.PRICE) as TOTAL_SALES
FROM USED_GOODS_BOARD b
JOIN USED_GOODS_USER u on b.WRITER_ID = u.USER_ID 
WHERE b.STATUS = 'DONE'
GROUP BY u.USER_ID
HAVING SUM(b.PRICE) >= 700000 
ORDER BY SUM(b.PRICE)

 

https://school.programmers.co.kr/learn/courses/30/lessons/131123

프로그래머스

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE,FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES) FAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

 

GROUP BY의 중요성 
GROUP을 형성할 때 대표 쿼리를 찾을 떄 서브쿼리를 사용하자

 

https://school.programmers.co.kr/learn/courses/30/lessons/157340

프로그래머스

 

SELECT CAR_ID, IF(MAX(IF(END_DATE >= '2022-10-16' AND START_DATE <= '2022-10-16', 1, 0)) = 1, '대여중', '대여 가능') AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

 

IF(조건절,True,False)

 

https://school.programmers.co.kr/learn/courses/30/lessons/151139

 

SELECT MONTH(c.START_DATE) AS MONTH, sb.CAR_ID, COUNT(c.HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY c
JOIN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE BETWEEN DATE("2022-08-01") AND DATE("2022-10-31")
    GROUP BY CAR_ID
    HAVING COUNT(HISTORY_ID) >= 5
) AS sb ON c.CAR_ID = sb.CAR_ID
WHERE DATE_FORMAT(START_DATE,'%Y%m') in ('202208','202209','202210')
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC;

 

서브쿼리에서car_id만추출하고있기때문에 메인쿼리에서 일치하는 car_id로데이터를 뽑을경우 기간에 관련없이 일치하는 모든 히스토리id가반환 <- 이걸 생각을 못해서 하루종일 헤맸다 하

 

https://school.programmers.co.kr/learn/courses/30/lessons/131532

프로그래머스

 

수정일 때 DISTINCT 생각하자

 

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/59413

 

 

WITH RECURSIVE CTE AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT NUM+1
    FROM CTE
    WHERE NUM < 23
)
SELECT NUM HOUR, IFNULL(COUNT(ANIMAL_ID),0) COUNT
FROM CTE
LEFT JOIN ANIMAL_OUTS ON CTE.NUM = HOUR(ANIMAL_OUTS.DATETIME)
GROUP BY HOUR
ORDER BY HOUR

 

가상 테이블 추가

 

WITH RECURSCIVE CTE AS (
  SELECT 0 AS NUM
  UNION ALL
  SELECT NUM + 1
  FROM CTE
  WHERE NUM < 23
)