서브쿼리 활용하기 - from 서브쿼리

 

# 문제

seoul_rent_bike

 

rent_station

 

 

# 문제

각 rent_station_num별로 사용 횟수를 구한 후 

rent_station의 각 구별로 사용 횟수의 합을 구하시오

 

# 핵심 개념

조인에서 from안에 테이블 뿐만 아니라

서브쿼리를 이용해서 테이블 중 on으로 조인할 컬럼과 count(*) as 별칭과 같이 밖에서 이용할 컬럼으로 써 줄 수 있다

 

 

# 정답

select borough,
	   sum(total_count) as total_usage
from (select rent_station_num,
		     count(*) as total_count
	  from seoul_rent_bike
	  group by rent_station_num) s
inner join rent_station r
on s.rent_station_num = r.rent_station_num 
group by 1
order by 2 desc

 

 

# 문제

다음의 쿼리문을 해석하시오

SELECT
  rs.rent_station,
  IFNULL(r.rent_count, 0) AS total_rentals,
  IFNULL(rt.return_count, 0) AS total_returns,
  (IFNULL(r.rent_count, 0) + IFNULL(rt.return_count, 0)) AS total_usage
FROM
  rent_station AS rs
LEFT JOIN
  (SELECT rent_station_num, COUNT(*) AS rent_count
   FROM seoul_rent_bike
   GROUP BY rent_station_num) AS r
ON rs.rent_station_num = r.rent_station_num
LEFT JOIN
  (SELECT return_station_num, COUNT(*) AS return_count
   FROM seoul_rent_bike
   GROUP BY return_station_num) AS rt
ON rs.rent_station_num = rt.return_station_num