하 진짜 조온나 어렵다
#1 카테고리
SELECT id AS categoryId, categoryName FROM category
#2 지역 선택 시 정보 가져 오기
SELECT sc.id as smallCityId, sc.smallCityName, bc.bigCityName, bc.id AS bigCityId FROM smallCity AS sc
INNER JOIN bigCity AS bc ON sc.smallCity_bigCity_id = bc.id
#3 지역 선택했을 시 추천수 높은 순대로 나오는 시설 정보
SELECT
review_and_facility.review_id, sc.id AS scId,
sc.smallCityName AS cityName,
review_and_facility.review,
review_and_facility.facilName,
facilImg.imgUrl,
round(avg(review_and_facility.rate), 1) AS rateAvg,
count(review_and_facility.review) AS reviewNumber,
roomTable.stayPrice,
roomTable.rentPrice,
roomTable.dc,
roomTable.checkInTime,
review_and_facility.location
FROM (((SELECT r.id AS review_id, review, rate, facil.id AS facilId, facil.geolocation AS location,facil.facilityName AS facilName, facil.rentFacility_smallCity_id AS facilScId
FROM review AS r JOIN rentFacility AS facil
ON facil.id = r.review_rentFacility_id) AS review_and_facility
JOIN smallCity AS sc ON sc.id = review_and_facility.facilScId)
JOIN rentFacilityImg AS facilImg
ON facilImg.id = review_and_facility.facilScId)
JOIN (
SELECT min(oneDayPrice) AS stayPrice,
min(hoursPrice) AS rentPrice,
room.room_rentFacility_id,
max(discount) AS dc,
checkInTime
FROM room
JOIN rentFacility ON rentFacility.id = room.room_rentFacility_id) as roomTable
ON roomTable.room_rentFacility_id = review_and_facility.facilId
ORDER BY rateAvg DESC
#4 유저 세부 정보 설정 화면
SELECT nickName, email, password, phoneNumber FROM user
#5 큰 도시 내에 있는 지하철 호선과 그 호선 이름, 호선에 해당하는 역 이름 가져오기
SELECT
stationName,
lineName,
(CASE WHEN bigCityName = '서울' OR '경기' OR '인천' THEN '수도권' ELSE bigCityName END) AS bigCityName
FROM station
JOIN subwayLine
ON station.station_line_id = subwayLine.id
JOIN bigCity
ON bigCity.id = subwayLine.line_bigCity_id
#6 숙소를 선택했을 시 보이는 방 리스트들
SELECT roomName,
hoursPrice,
onedayPrice,
maxHours,
checkInTime,
discount,
totalNumber,
(CASE WHEN availableNumber<= 0 THEN '예약 마감'
ELSE hoursPrice
END
) AS availableRent,
shortDescription,
standartPeopleNumber,
maximumPeopleNumber
FROM room
INNER JOIN rentFacility AS facil
ON facil.id = room.room_rentFacility_id
#7 MY야놀자 화면
SELECT
book_user.userId,
book_user.userNickName,
book_user.bookingTotal,
ltSelled.leisureTicketTotal,
tSelled.ticketTotal,
IF (isnull(p.totalPoint), 0, p.totalPoint ) AS totalPoint
FROM
(SELECT count(b.id) AS bookingTotal,
b.booking_user_id AS userId,
user.nickName AS userNickName
FROM booking AS b
INNER JOIN user
ON user.id = b.booking_user_id) AS book_user,
(SELECT count(ls.id) AS leisureTicketTotal
FROM leisureSelled AS ls
INNER JOIN user
ON user.id = ls.id) AS ltSelled,
(SELECT count(ts.id) AS ticketTotal
FROM ticketSelled AS ts
INNER JOIN user
ON user.id = ts.id) AS tSelled,
(SELECT
sum(amount) AS totalPoint
FROM point
INNER JOIN user
ON user.id = point.point_user_id) AS p
#8 객실 상세 정보 중 취소 약관, 예약 공지
SELECT
facilCancleNote.ccNote,
facilBookingNote.bkNote,
facilBookingNote.bkNoteId
FROM
(SELECT notation AS ccNote, cn.id AS ccNoteId
FROM cancelNotation AS cn
INNER JOIN rentFacility AS facil
ON facil.id = cn.cancelNote_facility_id ) AS facilCancleNote
RIGHT JOIN
(SELECT notation AS bkNote, bn.id AS bkNoteId
FROM bookingNotation AS bn
INNER JOIN rentFacility AS facil
ON facil.id= bn.bookingNotation_facility_id) AS facilBookingNote
ON facilCancleNote.ccNoteId = facilBookingNote.bkNoteId
#9 스탠다드 방을 골랐을 때
SELECT
*
FROM (SELECT
roomName,
room.id AS roomId,
shortDescription AS short,
maximumPeopleNumber AS maxPeople,
standartPeopleNumber AS avgPeople,
scName
FROM (SELECT
sc.id AS smallCityId,
sc.smallCityName AS scName,
facil.id AS facilId,
facil.facilityName AS facilName
FROM smallCity AS sc
JOIN rentFacility AS facil
ON facil.rentFacility_smallCity_id = sc.id) scAndFacil
JOIN room
ON scAndFacil.facilId = room.room_rentFacility_id) roomAndSc
LEFT JOIN roomImg
ON roomImg.img_room_id = roomAndSc.roomId
WHERE roomAndSc.roomId = 3
#10 레저 항목 하나 눌렀을 때
SELECT
scId,
leisureId,
imgUrl,
leisureName,
expirationDate,
facilityName,
smallCityName,
ticketName,
availableOnDay,
isWeekend,
numberRestrict,
denyDay,
price,
discount
FROM (SELECT
leisureImg.id AS imgId,
imgUrl,
leisureId,
scId,
smallCityName,
leisureName,
expirationDate,
facilityName
FROM
(SELECT
leisure.id AS leisureId,
sc.id AS scId,
smallCityName,
leisure.commodityName AS leisureName,
expirationDate,
facilityName
FROM smallCity AS sc
JOIN leisure
ON leisure.leisure_smallCity_id = sc.id) AS scAndLeisure
LEFT JOIN leisureImg
ON leisureImg.img_leisure_id = scAndLeisure.scId) AS imgAndLeisure
JOIN leisureTicketType AS ticket
ON ticket.ticketType_leisure_id = leisureId
#11 내가 id:1 유저라고 가정한 뒤 찜 목록 보기
SELECT
review_and_facility.review_id, sc.id AS scId,
sc.smallCityName AS cityName,
review_and_facility.review,
review_and_facility.facilName,
facilImg.imgUrl,
round(avg(review_and_facility.rate), 1) AS rateAvg,
count(review_and_facility.review) AS reviewNumber,
roomTable.stayPrice,
roomTable.rentPrice,
roomTable.dc,
roomTable.checkInTime,
review_and_facility.location,
review_and_facility.facilId
FROM
(((SELECT r.id AS review_id, review, rate, facil.id AS facilId, facil.geolocation AS location,facil.facilityName AS facilName, facil.rentFacility_smallCity_id AS facilScId
FROM review AS r JOIN rentFacility AS facil
ON facil.id = r.review_rentFacility_id) AS review_and_facility
JOIN smallCity AS sc ON sc.id = review_and_facility.facilScId)
JOIN rentFacilityImg AS facilImg
ON facilImg.id = review_and_facility.facilScId)
JOIN (
SELECT min(oneDayPrice) AS stayPrice,
min(hoursPrice) AS rentPrice,
room.room_rentFacility_id,
max(discount) AS dc,
checkInTime
FROM room
JOIN rentFacility ON rentFacility.id = room.room_rentFacility_id) as roomTable
ON roomTable.room_rentFacility_id = review_and_facility.facilId
JOIN keepingLocalRent AS keepLocal
ON keepLocal.keepingLocalRent_facility_id = review_and_facility.facilId
JOIN user
ON user.id = 1
#12 레저의 세부사항을 볼 때
SELECT * FROM (SELECT useInfo.id AS useInfoId, baseInfoId, leisureId, baseInformat, useInfo.info AS useInformat
FROM (SELECT baseInfo.id AS baseInfoId, leisure.id AS leisureId, baseInfo.info AS baseInformat
FROM leisureBasicInfo AS baseInfo
LEFT JOIN leisure
ON leisure.id = baseInfo.leisureBasicInfo_leisure_id) baseInfoAndLeisure
JOIN leisureUseInfo AS useInfo
ON useInfo.leisureUseInfo_leisure_id = baseInfoAndLeisure.leisureId) useInfoLeisure
JOIN leisureNotice AS notice
ON notice.leisureNotice_leisure_id = leisureId
WHERE leisureId = 1
#13 선착순 쿠폰 리스트 보기
SELECT
categoryName, cnt, facilName, facilId, smallCityName, maxHours, hoursPrice
FROM
(SELECT
categoryName, cnt, facilName, facilId, scId, smallCityName, categoryId
FROM (SELECT categoryName, cnt, facilName, facilId, scId, categoryId
FROM
(SELECT cnt, facil.facilityName AS facilName,
facilId,
facil.rentFacility_smallCity_id AS scId,
facil.rentFacility_category_id AS categoryId
FROM
(SELECT count(id) AS cnt, fcc.coupon_facility_id AS facilId
from firstComeCoupon AS fcc
GROUP BY fcc.coupon_facility_id) AS facilGroup
JOIN rentFacility AS facil
ON facil.id = facilGroup.facilId) facilAndCoupon
JOIN category
ON category.id = facilAndCoupon.categoryId) ctgAndFacilAndFcc
JOIN smallCity AS sc
ON sc.id = ctgAndFacilAndFcc.scId) scAndFcc
JOIN room AS r
ON r.room_rentFacility_id = scAndFcc.facilId
#14 특정 시설 리뷰 보기
SELECT
review, reviewNumber, rateAvg, room.roomName
FROM
(SELECT
reviewNumber, review, rateAvg, user.nickName AS userName, user.id AS userId
FROM (SELECT count(review) AS reviewNumber, round(avg(rate), 1) AS rateAvg, review.review_rentFacility_id = 2 AS facilId
FROM review
WHERE review.createdAt >= date_add(now(), interval -6 month) AND review.review_rentFacility_id = 2) reviewFacil2
RIGHT JOIN review
ON review.review_rentFacility_id = 2 = reviewFacil2.facilId
JOIN USER
ON review.review_user_id = user.id) userAndReview
LEFT JOIN booking
ON booking.booking_user_id = userAndReview.userId
LEFT JOIN room
ON booking.booking_room_id = room.id
GROUP BY review
천방지축 CEO & CTO