[SQL문제풀기] 3년간 들어온 소장품 집계하기

silver

Mar 04, 2025

Contents

문제 SQLite

문제

school.programmers.co.kr

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

SQLite

내가 작성한 오답

: 2014, 2015, 2016에 존재하지 않는 classification도 출력되어야 한다. 레코드가 22개만 출력됨

→ 28개의 레코드가 출력되어야 한다.


with a as (
    select
      classification,
      strftime ('%Y', acquisition_date) year,
      count(artwork_id) num
    from artworks
    where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016')
    group by classification, strftime ('%Y', acquisition_date)
  ), 
  b as (select classification, 
        case year when '2014' then num else null end b2014,
        case year when '2015' then num else null end b2015,
        case year when '2016' then num else null end b2016
      from a)
select classification, ifnull(sum(b2014),0) '2014', 
      ifnull(sum(b2015),0) '2015', ifnull(sum(b2016),0) '2016'
from b
group by classification

내가 수정한 정답

: classification에 distinct를 적용하지 않으면 classification이 여러번 나온다면 그 때마다 값이 더해진다

distinct 적용하지 않은 결과

distinct 적용한 결과


with a as (select classification, strftime ('%Y', acquisition_date) year, count(artwork_id) num
          from artworks
          where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016')
          group by classification, strftime ('%Y', acquisition_date)), 
      b as (select classification, 
            case year when '2014' then num else null end b2014,
            case year when '2015' then num else null end b2015,
            case year when '2016' then num else null end b2016
          from a)
select a.classification, ifnull(sum(b.b2014),0) '2014', 
      ifnull(sum(b.b2015),0) '2015', ifnull(sum(b.b2016),0) '2016'
from b b
right join (select distinct classification from artworks) a on b.classification= a.classification
group by a.classification

with a as ( select classification, strftime ('%Y', acquisition_date) year, count(artwork_id) num from artworks where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016') group by classification, strftime ('%Y', acquisition_date) ), b as (select classification, case year when '2014' then num else null end b2014, case year when '2015' then num else null end b2015, case year when '2016' then num else null end b2016 from a) select classification, ifnull(sum(b2014),0) '2014', ifnull(sum(b2015),0) '2015', ifnull(sum(b2016),0) '2016' from b group by classification

with a as (select classification, strftime ('%Y', acquisition_date) year, count(artwork_id) num from artworks where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016') group by classification, strftime ('%Y', acquisition_date)), b as (select classification, case year when '2014' then num else null end b2014, case year when '2015' then num else null end b2015, case year when '2016' then num else null end b2016 from a) select a.classification, ifnull(sum(b.b2014),0) '2014', ifnull(sum(b.b2015),0) '2015', ifnull(sum(b.b2016),0) '2016' from b b right join (select distinct classification from artworks) a on b.classification= a.classification group by a.classification