당근 마켓 클론 한 방 쿼리😎

1. 메인(홈) 페이지

• 필요한 정보 : roadNameAddress(유저가 거주하는), ****productImageUrl / productIdx, title, location, price, isUpdated, productTime, count(ProductChatting), count(WishList)

#====메인 페이지 한방 쿼리====#
#현재는 유저와 같은 동네에 있는 물건만 뜨게 쿼리를 작성하였습니다.
#추후에는 상품의 location을 직접 입력 받아 여러 지역의 물건이 뜨게 쿼리를 수정할 수 있습니다.
#ex) where p.location = '~~동'
select p.productIdx, p.title as 상품이름, p.price as 상품가격, uaa.roadNameAddress as 동네이름, pi.productImageUrl, ifnull(pc.cnt, 0) as chattingRoomCount,
       ifnull(wl.cnt, 0) as likeCount,
       @isUpdated := (case
                        when p.createdAt != p.updatedAt
                        then '끌올'
                        else '초기생성'
                    end) as isUpdated,
        case
            when @isUpdated = '끌올' and timestampdiff(second, p.updatedAt, now()) < 60
               then concat(timestampdiff(second, p.updatedAt, now()), '초 전')
           when @isUpdated = '끌올' and timestampdiff(minute, p.updatedAt, now()) < 60
               then concat(timestampdiff(minute, p.updatedAt, now()), '분 전')
           when @isUpdated = '끌올' and timestampdiff(hour, p.updatedAt, now()) < 60
               then concat(timestampdiff(hour, p.updatedAt, now()), '시간 전')
           when @isUpdated = '끌올' and timestampdiff(hour, p.updatedAt, now()) >= 60
               then concat(timestampdiff(day, p.updatedAt, now()), '일 전')
           when @isUpdated = '초기생성' and timestampdiff(second, p.createdAt, now()) < 60
               then concat(timestampdiff(second, p.createdAt, now()), '초 전')
           when @isUpdated = '초기생성' and timestampdiff(minute, p.createdAt, now()) < 60
               then concat(timestampdiff(minute, p.createdAt, now()), '분 전')
           when @isUpdated = '초기생성' and timestampdiff(hour, p.createdAt, now()) < 60
               then concat(timestampdiff(hour, p.createdAt, now()), '시간 전')
           when @isUpdated = '초기생성' and timestampdiff(hour, p.createdAt, now()) >= 60
               then concat(timestampdiff(day, p.createdAt, now()), '일 전')
        end as productTime
from (select * from Product where Product.status = 'Y') p
    join (select ua.addressIdx, a.roadNameAddress from UserAddress ua join Address a on ua.userIdx = 1 and ua.addressIdx = a.addressIdx) uaa
        on p.location = uaa.addressIdx
    left join ProductImage pi on p.productIdx = pi.productIdx and pi.status = 'Y' and pi.isMain = 'T'
    left join (select pc.productIdx, count(pc.productIdx) as cnt
                from ProductChatting pc
                where pc.status = 'Y'
                group by pc.productIdx) pc on p.productIdx = pc.productIdx
    left join (select wl.productIdx, count(wl.userIdx) as cnt
                from WishList wl
                where wl.status = 'Y'
                group by wl.productIdx) wl on p.productIdx = wl.productIdx;

2. 동네 생활 메인 페이지

• 필요한 정보 : 접속한 사용자가 거주하는 동네, 게시물 아이디, 게시물 제목, 게시물 내용, 게시물 타입, 게시물 작성자 이름, 게시물 작성자의 거주지(현재는 유저의 거주지와 동일), 궁금해요 수, 커멘트 수, 작성 시간, 게시물 이미지

#현재는 유저와 같은 동네에 있는 게시물만 뜨게 쿼리를 작성하였습니다.
#추후에는 게시물의 location을 직접 입력 받아 여러 지역의 물건이 뜨게 쿼리를 수정할 수 있습니다.
#ex) where p.location = '~~동'
#게시물 사진은 3장 까지 보여줄 수 있는데 이러한 비즈니스 로직은 빼고 쿼리를 작성하였습니다.
#사진이 여러개 일 경우 사진의 개수 만큼 로우가 생성된다.
#게시글에 달린 공감의 종류에 따라 행이 생성된다.
#게시글에 달린 댓글 수를 보여준다.
#게시글이 질문 타입일 경우 궁금해요를 몇개 받았는지 보여준다.
#공감 종류까지 불러오면 데이터의 중복이 생기기 때문에 불러오지 않았다.
select p.postIdx, p.writerIdx, u.nickname, a.roadNameAddress, p.title, p.content, pt.typeName as postTypeName, pi.postImageUrl,
       pcsc.commentCount, pcsc.sympathyCount, pcsc.curiousCount,
       case
           when timestampdiff(second, p.createdAt, now()) < 60
               then concat(timestampdiff(second, p.createdAt, now()), '초 전')
           when timestampdiff(minute, p.createdAt, now()) < 60
               then concat(timestampdiff(minute, p.createdAt, now()), '분 전')
           when timestampdiff(hour, p.createdAt, now()) < 60
               then concat(timestampdiff(hour, p.createdAt, now()), '시간 전')
           when timestampdiff(hour, p.createdAt, now()) >= 60
               then concat(timestampdiff(day, p.createdAt, now()), '일 전')
        end as postTime
from (select * from Post p where p.status = 'Y') p
    join (select a.addressIdx, a.roadNameAddress
            from UserAddress ua
            join Address a on ua.addressIdx = a.addressIdx and ua.userIdx = 1) uaa on p.location = uaa.addressIdx
    join PostType pt on p.postTypeIdx = pt.postTypeIdx
    left join PostImage pi on p.postIdx = pi.postIdx and pi.status = 'Y'
    left join (select pcs.postIdx, pcs.title, pcs.commentCount, pcs.sympathyCount, count(c.curiousIdx) as curiousCount
                from (select pc.postIdx, pc.title, pc.commentCount, count(ps.sympathyIdx) as sympathyCount
                from (select p.postIdx, p.title, count(c.commentIdx) as commentCount
                        from (select * from Post p where p.status = 'Y') p
                            left join Comment c on p.postIdx = c.postIdx
                            group by p.postIdx) pc
                                left join PostSympathy ps on pc.postIdx = ps.postIdx
                                group by pc.postIdx) pcs
                                    left join Curious c on pcs.postIdx = c.postIdx
                                    group by pcs.postIdx) pcsc on p.postIdx = pcsc.postIdx
    join User u on p.writerIdx = u.userIdx
    join UserAddress ua on u.userIdx = ua.userIdx
    join Address a on a.addressIdx = ua.addressIdx;

3. 에어팟 판매자가 살고 있는 동네에서 거래되는 모든 물건

#====에어팟 판매자가 살고 있는 동네에서 팔고 있는 모든 물건====/
select p.productIdx, p.title
from Product p
    join (select u.nickname, p.title, a.roadNameAddress, a.addressIdx
                        from Product p
                            join User u on p.sellerIdx = u.userIdx
                            join UserAddress ua on u.userIdx = ua.userIdx
                            join Address a on ua.addressIdx = a.addressIdx
                        where p.title = '에어팟'
) pa on p.location = pa.addressIdx;
#====에어팟 판매자가 살고 있는 동네에서 팔고 있는 모든 물건====/

4. 사용자가 구매한 상품 정보

#====사용자가 구매한 상품 정보====#
select ud.userIdx, ud.userName, ud.dealIdx, ud.dealPrice, p.productIdx, p.title
from (select d.dealIdx, d.productIdx, d.dealPrice, u.userIdx, u.userName from Deal d join User u on d.buyerIdx = u.userIdx) ud
    join Product p on ud.productIdx = p.productIdx;
select * from Product;
select * from Deal;
select d.dealIdx, u.userIdx from Deal d join User u on d.buyerIdx = u.userIdx;
#====사용자가 구매한 상품 정보====#

5. Cross join & Inner Join

#====cross join====#
select u.userIdx, u.userName, p.productIdx, p.title
from User u, Product p
where u.userIdx = 1 and u.userIdx = p.sellerIdx;
-- 두 테이블을 cross join 후 where 질의를 거치기 때문에 수행시간이 길다.

#====inner join====#
select u.userIdx, u.userName, p.productIdx, p.title
from Product p
    join User u on u.userIdx = p.sellerIdx and u.userIdx = 1;
-- inner join을 사용하면 애초에 join 할 때 조건을 만족 시키는 행끼리만 join 하기 때문에 수행시간이 짧다.

• cross join : 결과가 두 테이블의 각 행의 개수를 곱한 것과 같다
  • 카디션 곱이라고도 부름
• inner join : 두 테이블의 교집합