MYSQL - searchview 생성까지의 과정 정리

-- 평점 --
select gh.guestHouseCode, gh.guestHouseName, gh.avgRating, gr.recommendRating
from guestHouse_tb gh, grade_tb gr
where gr.minRating < gh.avgRating and gh.avgRating <= gr.maxRating;

select * from grade_tb;

-- 태그 --
select gh.guestHouseCode, gh.guestHouseName, gh.businessTrip, gh.gourmet, gh.trip, gh.shopping
from guestHouse_tb gh
;

-- 게하사진 --
select gh.guestHouseCode, gh.guestHouseName, fi.originalName
from guestHouse_tb gh
	join file_tb fi
	on fi.guestHouseCode = gh.guestHouseCode
;

-- 게스트하우스 방중 최저가격 --
select gh.guestHouseCode, MIN(rm.charge) "minprice"
from guestHouse_tb gh, room_tb rm
where rm.guestHouseCode = gh.guestHouseCode
group by gh.guestHouseCode;

select gh.guestHouseCode, MIN(rm.charge) "minprice"
from guestHouse_tb gh
	join room_tb rm
	on rm.guestHouseCode = gh.guestHouseCode
group by gh.guestHouseCode;

-- 평점 * 태그 --
select gh.guestHouseCode, gh.guestHouseName, gh.avgRating, gr.recommendRating,
	   gh.businessTrip, gh.gourmet, gh.trip, gh.shopping
from guestHouse_tb gh
	join grade_tb gr
	on gr.minRating <= gh.avgRating and gh.avgRating <= gr.maxRating
;

-- 평점 * 태그 * 최저가격 --
select gh.guestHouseCode, gh.guestHouseName, gh.avgRating, gr.recommendRating,
	   c.minprice, gh.businessTrip, gh.gourmet, gh.trip, gh.shopping
from guestHouse_tb gh
	join grade_tb gr
	on gr.minRating <= gh.avgRating and gh.avgRating <= gr.maxRating
	join
		(
		select gh.guestHouseCode, MIN(rm.charge) "minprice"
		from guestHouse_tb gh
			join room_tb rm
			on rm.guestHouseCode = gh.guestHouseCode
		group by gh.guestHouseCode
		) c
	on c.guestHouseCode = gh.guestHouseCode
;

-- 게스트하우스 별 모든 방 --
select gh.guestHouseCode, gh.guestHouseName, rm.roomCode
from guestHouse_tb gh
	join room_tb rm
	on rm.guestHouseCode = gh.guestHouseCode
;

-- 모든 게스트하우스의 방 갯수  --
select gh.guestHouseCode, count(rm.roomCode) "roomCnt"
from guestHouse_tb gh
	join room_tb rm
	on rm.guestHouseCode = gh.guestHouseCode
group by gh.guestHouseCode
;

select * from review_tb;
select * from booking_tb;
select * from room_tb;

-- 모든 게스트하우스에 달린 리뷰개수(없으면 0) --
select gh.guestHouseCode, count(rv.reviewNo)
from guestHouse_tb gh
	join room_tb rm
	on rm.guestHouseCode = gh.guestHouseCode
	left join booking_tb bk
	on bk.roomCode = rm.roomCode
	left join review_tb rv
	on rv.bookingCode = bk.bookingCode
group by gh.guestHouseCode
;

-- 모든 게스트하우스의 평점 * 태그 * 최저가격 * 리뷰개수 * 이미지 * 주소 --
select gh.guestHouseCode, gh.guestHouseName, gh.address, gh.avgRating, gr.recommendRating,
	   ghmin.minprice, gh.businessTrip, gh.gourmet, gh.trip, gh.shopping,
	   rvc.reviewCnt, fi.originalName
from guestHouse_tb gh
	left join file_tb fi
	on fi.guestHouseCode = gh.guestHouseCode
	join grade_tb gr
	on gr.minRating <= gh.avgRating and gh.avgRating <= gr.maxRating
	join
		(
		select gh.guestHouseCode, MIN(rm.charge) "minprice"
		from guestHouse_tb gh
			join room_tb rm
			on rm.guestHouseCode = gh.guestHouseCode
		group by gh.guestHouseCode
		) ghmin
	on ghmin.guestHouseCode = gh.guestHouseCode
	left join
		(
		select gh.guestHouseCode, count(rv.reviewNo) "reviewCnt"
		from guestHouse_tb gh
			join room_tb rm
			on rm.guestHouseCode = gh.guestHouseCode
			left join booking_tb bk
			on bk.roomCode = rm.roomCode
			left join review_tb rv
			on rv.bookingCode = bk.bookingCode
		group by gh.guestHouseCode
		) rvc
	on rvc.guestHouseCode = gh.guestHouseCode
;

-- 위 코드 뷰테이블로 만들기 --
create view searchview as
select gh.guestHouseCode, gh.guestHouseName, gh.address, gh.avgRating, gr.recommendRating, ghmin.minprice,
	   gh.businessTrip, gh.gourmet, gh.trip, gh.shopping, rvc.reviewCnt, fi.originalName
from guestHouse_tb gh
	left join file_tb fi
	on fi.guestHouseCode = gh.guestHouseCode
	join grade_tb gr
	on gr.minRating <= gh.avgRating and gh.avgRating <= gr.maxRating
	join
		(
		select gh.guestHouseCode, MIN(rm.charge) "minprice"
		from guestHouse_tb gh
			join room_tb rm
			on rm.guestHouseCode = gh.guestHouseCode
		group by gh.guestHouseCode
		) ghmin
	on ghmin.guestHouseCode = gh.guestHouseCode
	left join
		(
		select gh.guestHouseCode, count(rv.reviewNo) "reviewCnt"
		from guestHouse_tb gh
			join room_tb rm
			on rm.guestHouseCode = gh.guestHouseCode
			left join booking_tb bk
			on bk.roomCode = rm.roomCode
			left join review_tb rv
			on rv.bookingCode = bk.bookingCode
		group by gh.guestHouseCode
		) rvc
	on rvc.guestHouseCode = gh.guestHouseCode
;

select * from searchview;