쿠팡 DB 설계 최종 , 한방 쿼리 예시

쿠팡 ERD

---

URL : https://aquerytool.com/aquerymain/index/?rurl=d8467984-5fa4-491c-b567-1b1de339fc1c&
Password : xy2cgb

 

한방 쿼리

---

- 내 정보 관리

use kiinDB;
select name                     as 고객명,
       passwd                   as 비밀번호,
       email                    as 이메일,
       number                   as 연락처,
       address                  as 주소,
       profile_img              as 프로필사진,
       concat(membership, '회원') as 멤버십
from user u
         inner join (select membership, user_id from membership) m on u.id = m.user_id
         inner join (select address, user_id, min(id) from address) a on u.id = a.user_id
where u.id = 1

- 주문목록

use kiinDB;
select o.created_at                      as 주문일자,
       o.updated_at,
       shipment                          as 배송종류,
       shipment_img_url                  as 배송이미지,
       concat(name, space(1), o2.option) as 상품이름,
       price                             as 가격,
       stock                             as 주문개수,
       prod_img                          as 상품이미지,
       (case
            when
                o.status = 'Y'
                then '배송완료'
            else '배송 중'
           end)                          as 배송상태,
       (case
            when
                o.status = 'Y' and datediff(o.updated_at,current_timestamp) < 1
                then concat('어제',space(1),date_format(o.updated_at,'%m/%d/%W'),space(1),'도착')
           when
                o.status = 'Y' and datediff(o.updated_at,current_timestamp) > 1
                then concat(date_format(o.updated_at,'%m/%d/%W'),'도착')
            when o.status= 'N' and date_format(o.created_at,'%h') < 12
           then '오늘 오후 도착 보장'
           else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY),'%m/%d/%W'),'도착보장')
           end)                          as 배송상태

from order_ o
         inner join shipments_mapping sm on o.prod_id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join product p on o.prod_id = p.id
         inner join option_mapping om on p.id = om.prod_id
         inner join option_ o2 on om.option_id = o2.id
where o.buyer_id = 1
  and o2.id = 1;

 

-주문상세

use kiinDB;
select user.name as 이름,
       payment as 결제수단,
       number as 핸드폰번호,
        address as 주소,
        o.created_at                      as 구입일자,
       o.id                              as 주문번호,
       shipment                          as 배송종류,
       shipment_img_url                  as 배송이미지,
       (case
            when s.id = '1' then '0원'
            when s.id = '2' then '0원'
            else '3000원' end)            as 배송비,
       concat(p.name, space(1), o2.option) as 상품이름,
       price                             as 가격,
       stock                             as 주문개수,
       prod_img                          as 상품이미지,
       (case
            when
                o.status = 'Y'
                then '배송완료'
            else '배송 중'
           end)                          as 배송상태,
       (case
            when
                o.status = 'Y' and datediff(o.updated_at, current_timestamp) < 1
                then concat('어제', space(1), date_format(o.updated_at, '%m/%d/%W'), space(1), '도착')
            when
                o.status = 'Y' and datediff(o.updated_at, current_timestamp) > 1
                then concat(date_format(o.updated_at, '%m/%d/%W'), '도착')
            when o.status = 'N' and date_format(o.created_at, '%h') < 12
                then '오늘 오후 도착 보장'
            else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY), '%m/%d/%W'), '도착보장')
           end)                          as 배송상태

from order_ o
         inner join shipments_mapping sm on o.prod_id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join product p on o.prod_id = p.id
         inner join option_mapping om on p.id = om.prod_id
         inner join (select min(id),'option',id from option_) o2 on om.option_id = o2.id
         inner join user on o.buyer_id = user.id
        left join (select min(id),address,user_id from address) a on user.id = a.user_id
inner join order_payment op on op.order_id = o.id
inner join payments_list pl on pl.id = op.payment_id
where o.id = 5;

-최근 본 상품

use kiinDB;
select u.id,
       p.prod_img,
       p.name,
       p.price     AS 원래가격,
       round((p.price - p.price*(discount/100)),0)   AS 할인가,
       (case when p.status='Y' then '구매가능'
           when p.status='N' then '품절' end)    AS 품절유무,
       shipment    AS 배송,
       avgscore    AS 평균별점,
       reviewCount AS 리뷰개수
from user u
         inner join recently_viewed rv on u.id = rv.user_id
         inner join product p on rv.prod_id = p.id
         inner join shipments_mapping sm on p.id = sm.prod_id
         inner join shipments s on sm.shipment_id = s.id
         inner join (select prod_id, count(r.id) AS reviewCount, avg(score) AS avgscore
                     from review r
                              inner join score s2 on r.id = s2.review_id
                     group by prod_id) r on p.id = r.prod_id
        left join (select discount,prod_id from discount_prod) dp on p.id = dp.prod_id
where u.id = 1
group by p.id
limit 20

-상품 페이지

use kiinDB;
select prod_img,price AS 원래가, round((p.price - p.price*(discount/100)),0)   AS 할인가,name,brand,o.`option` AS 옵션,avgScore AS 평균별점, 리뷰수, shipment
from product p
         inner join (select count(r.id) AS 리뷰수, round(avg(score),0) AS avgScore,prod_id from review r inner join score s on r.id = s.review_id group by prod_id) r on r.prod_id= p.id
         inner join shipments_mapping sm on p.id = sm.prod_id
         inner join shipments s2 on sm.shipment_id = s2.id
        inner join brand b on p.brand_id = b.id
        inner join option_mapping om on p.id = om.prod_id
        inner join (select min(id) AS id,`option`  from option_) o on om.option_id = o.id
        inner join discount_prod dp on p.id = dp.prod_id
where p.id = 1