DAY 25-29
MINI PROJECT 02 (TEAM PROJECT)
Condition
-쿠팡 페이지 데이터 구조 분석 및 데이터 베이스 생성
-데이터 분석 sheet (정규화 과정)
-테이블 명세, ER 다이어그램 (relationship unit : table or data)
-카테고리(최상위 카테고리만-하위제외)
-상품정보,주문옵션,배송관련, 장바구니
-상품상세정보, 상품평, 상품믄의 - 답글, 이미지, 신고하기(신고 기록 테이블), 도움돼요 등등 고려
-판매자 정보
-회원정보
- SQL- 각 테이블 별 insert 구문
select 구문 : 상품명 검색, 판매자 검색, 카테고리 선택, (할인된 가격 계산), 가격 범위, 날짜 범위, 총 카운트, 페이지 카운트 , 페이징 처리(limit, offset)
if we need view, create view
Create Table Sql문
-- create database coupang;
-- use coupang;
create table if not exists product_brief_info (
pbri_seq int not null auto_increment primary key ,
pbri_pbi_seq int not null ,
pbri_content text not null
);
create table if not exists category_info (
cate_seq int not null auto_increment primary key,
cate_parent_seq int null ,
cate_name varchar(20) not null
);
create table if not exists product_basic_info (
pbi_seq int not null auto_increment primary key,
pbi_name varchar(250) not null ,
pbi_price int not null default 0 ,
pbi_increase_price int not null default 0 ,
pbi_discount_rate int null ,
pbi_wow_discount_rate int null ,
pbi_point int null ,
pbi_max_point int null ,
pbi_status int not null default 0 ,
pbi_manu_seq int not null
);
create table if not exists manufacturer_info (
manu_seq int not null auto_increment primary key,
manu_name varchar(255) not null
);
create table if not exists mandatory_notice (
mn_seq int not null auto_increment primary key,
mn_manu_seq int not null ,
mn_prod_dt date not null ,
mn_origin varchar(50) not null ,
mn_capacity varchar(255) null ,
mn_counsel_num varchar(255) not null ,
mn_caution text null ,
mn_pbi_seq int not null
);
create table if not exists mandatory_electronics_detail_info (
medi_seq int not null auto_increment primary key,
medi_warranty_standard varchar(255) null ,
medi_pc_seq varchar(255) null ,
medi_voltage_power varchar(30) null ,
medi_energy_rating varchar(30) null ,
medi_extra_fee int null ,
medi_prod_comp varchar(255) null ,
medi_prod_size varchar(50) not null ,
medi_prod_area varchar(255) null ,
medi_pbi_seq int not null
);
create table if not exists manadatory_food_detail_info (
mfdi_seq int not null auto_increment primary key,
mfdi_import_phase int not null default 1 ,
mfdi_prod_comp varchar(255) null ,
mfdi_expiration_dt datetime not null ,
mfdi_detailed_item_note text null ,
mfdi_pbi_seq int not null
);
create table if not exists mandatory_cosmetic_detail_info (
mcdi_seq int not null auto_increment primary key,
mcdi_main_facts varchar(255) null ,
mcdi_expiration_dt date not null ,
mcdi_ingredients text null ,
mcdi_relevant_laws text null ,
mcdi_warranty_standard varchar(255) null ,
mcdi_pbi_seq int not null
);
create table if not exists seller_info (
si_seq int not null auto_increment primary key,
si_name varchar(50) not null ,
si_id varchar(50) not null ,
si_pwd varchar(50) not null ,
si_address varchar(255) null ,
si_phone varchar(50) null ,
si_business_num varchar(50) null
);
create table if not exists product_detail_info (
pdi_seq int not null auto_increment primary key,
pdi_content text not null ,
pdi_type int not null ,
pdi_order int not null ,
pdi_pbi_seq int not null
);
create table if not exists product_cover_images (
pcimg_seq int not null auto_increment primary key,
pcimg_file_name varchar(255) not null ,
pcimg_pbi_seq int not null ,
pcimg_order int not null
);
create table if not exists review_info (
ri_seq int not null auto_increment primary key,
ri_mi_seq int not null ,
ri_score int not null ,
ri_reg_dt datetime not null default now(),
ri_si_seq int not null ,
ri_pbi_seq int not null ,
ri_pbo_seq int not null ,
ri_content text not null
);
create table if not exists review_by_item (
rbi_seq int not null auto_increment primary key,
rbi_pbi_seq int not null ,
rbi_name varchar(100) not null ,
rbi_order int not null
);
create table if not exists review_by_item_choice (
rbic_seq int not null auto_increment primary key,
rbic_rbi_seq int not null ,
rbic_name varchar(200) not null ,
rbic_order int not null
);
create table if not exists review_by_item_customer_choice (
rbicc_seq int not null auto_increment primary key,
rbicc_mi_seq int not null ,
rbicc_rbi_seq int not null ,
rbicc_rbic_seq int not null
);
create table if not exists member_info (
mi_seq int not null auto_increment primary key,
mi_name varchar(255) not null ,
mi_id varchar(20) not null ,
mi_pwd varchar(64) not null ,
mi_birth date not null ,
mi_reg_dt datetime not null default now(),
mi_email varchar(100) not null ,
mi_gen int not null default 0,
mi_phone varchar(20) not null ,
mi_mg_seq int not null default 0,
mi_address varchar(255) not null ,
mi_coupay int not null default 1
);
create table if not exists product_buy_option_category (
pboc_seq int not null auto_increment primary key,
pboc_pbi_seq int not null ,
pboc_title varchar(255) not null
);
create table if not exists product_brief_info (
pbri_seq int not null auto_increment primary key ,
pbri_pbi_seq int not null ,
pbri_content text not null
);
create table if not exists category_info (
cate_seq int not null auto_increment primary key,
cate_parent_seq int not null ,
cate_name varchar(20) not null
);
create table if not exists product_basic_info (
pbi_seq int not null auto_increment primary key,
pbi_name varchar(250) not null ,
pbi_price int not null default 0 ,
pbi_increase_price int not null default 0 ,
pbi_discount_rate int null ,
pbi_wow_discount_rate int null ,
pbi_point int null ,
pbi_max_point int null ,
pbi_status int not null default 0 ,
pbi_manu_seq int not null
);
create table if not exists manufacturer_info (
manu_seq int not null auto_increment primary key,
manu_name varchar(255) not null
);
create table if not exists mandatory_notice (
mn_seq int not null auto_increment primary key,
mn_manu_seq int not null ,
mn_prod_dt date not null ,
mn_origin varchar(50) not null ,
mn_capacity varchar(255) null ,
mn_counsel_num varchar(255) not null ,
mn_caution text null ,
mn_pbi_seq int not null
);
create table if not exists mandatory_electronics_detail_info (
medi_seq int not null auto_increment primary key,
medi_warranty_standard varchar(255) null ,
medi_pc_seq varchar(255) null ,
medi_voltage_power varchar(30) null ,
medi_energy_rating varchar(30) null ,
medi_extra_fee int null ,
medi_prod_comp varchar(255) null ,
medi_prod_size varchar(50) not null ,
medi_prod_area varchar(255) null ,
medi_pbi_seq int not null
);
create table if not exists manadatory_food_detail_info (
mfdi_seq int not null auto_increment primary key,
mfdi_import_phase int not null default 1 ,
mfdi_prod_comp varchar(255) null ,
mfdi_expiration_dt datetime not null ,
mfdi_detailed_item_note text null ,
mfdi_pbi_seq int not null
);
create table if not exists mandatory_cosmetic_detail_info (
mcdi_seq int not null auto_increment primary key,
mcdi_main_facts varchar(255) null ,
mcdi_expiration_dt date not null ,
mcdi_ingredients text null ,
mcdi_relevant_laws text null ,
mcdi_warranty_standard varchar(255) null ,
mcdi_pbi_seq int not null
);
create table if not exists seller_info (
si_seq int not null auto_increment primary key,
si_name varchar(50) not null ,
si_id varchar(50) not null ,
si_pwd varchar(50) not null ,
si_address varchar(255) null ,
si_phone varchar(50) null ,
si_business_num varchar(50) null
);
create table if not exists product_detail_info (
pdi_seq int not null auto_increment primary key,
pdi_content text not null ,
pdi_type int not null ,
pdi_order int not null ,
pdi_pbi_seq int not null
);
create table if not exists product_cover_images (
pcimg_seq int not null auto_increment primary key,
pcimg_file_name varchar(255) not null ,
pcimg_pbi_seq int not null ,
pcimg_order int not null
);
create table if not exists review_info (
ri_seq int not null auto_increment,
ri_mi_seq int not null ,
ri_score int not null ,
ri_reg_dt datetime not null default now(),
ri_si_seq int not null ,
ri_pbi_seq int not null ,
ri_pbo_seq int not null ,
ri_content text not null
);
create table if not exists review_by_item (
rbi_seq int not null auto_increment primary key,
rbi_pbi_seq int not null ,
rbi_name varchar(100) not null ,
rbi_order int not null
);
create table if not exists review_by_item_choice (
rbic_seq int not null auto_increment primary key,
rbic_rbi_seq int not null ,
rbic_name varchar(200) not null ,
rbic_order int not null
);
create table if not exists review_by_item_customer_choice (
rbicc_seq int not null auto_increment primary key,
rbicc_mi_seq int not null ,
rbicc_rbi_seq int not null ,
rbicc_rbic_seq int not null
);
create table if not exists member_info (
mi_seq int not null auto_increment primary key,
mi_name varchar(255) not null ,
mi_id varchar(20) not null ,
mi_pwd varchar(64) not null ,
mi_birth date not null ,
mi_reg_dt datetime not null default now(),
mi_email varchar(100) not null ,
mi_gen int not null default 0,
mi_phone varchar(20) not null ,
mi_mg_seq int not null default 0,
mi_address varchar(255) not null ,
mi_coupay int not null default 1
);
create table if not exists product_buy_option_category (
pboc_seq int not null auto_increment primary key,
pboc_pbi_seq int not null ,
pboc_title varchar(255) not null
);
create table if not exists product_certification (
pc_seq int not null auto_increment primary key,
pc_num varchar(50) not null ,
pc_pbi_seq int not null,
pc_ci_seq int not null
);
create table if not exists certification_info (
pc_seq int not null auto_increment primary key,
pc_num varchar(50) not null,
pc_pbi_seq int not null ,
pc_ci_seq int not null
);
create table if not exists point_info (
pi_seq int not null auto_increment primary key,
pi_pbi_seq int not null,
pi_member_type int not null default 0 ,
pi_point int null ,
pi_nax_point int null
);Insert Value Sql문
INSERT INTO coupang.category_info
(cate_parent_seq, cate_name)
VALUES
(null,'패션의류/잡화'),(null,'뷰티'),(null,'출산/유아동'),(null,'식품'),(null,'주방용품'),
(null,'생활용품'),(null,'홈인테리어'),(null,'가전디지털'),(null,'스포츠/레저'),(null,'자동차용품'),
(null,'도서/음반/DVD'),(null,'완구/취미'),(null,'문구/오피스'),(null,'반려동물용품'),(null,'헬스/건강식품');
INSERT INTO coupang.category_info
(cate_parent_seq, cate_name)
VALUES
(1,'여성패션'),(1,'남성패션'),(1,'남녀 공동 의류'),(1,'유아동패션'),
(2,'명품뷰티'),(2,'스킨케어'),(2,'클린/비건뷰티'),(2,'클렌징/필링'),
(2,'더마코스메틱'),(2,'메이크업'),(2,'향수'),(2,'남성화장품'),(2,'네일'),
(2,'뷰티소품'),(2,'어린이화장품'),(2,'로드샵'),(2,'헤어'),(2,'바디'),
(2,'선물세트/키트'),(3,'유아동패션'),(3,'기저귀'),(3,'물티슈'),(3,'분유/어린이식품'),
(3,'어린이 건강식품'),(3,'수유용품'),(3,'이유용품/유아식기'),(3,'매트/안전용품'),(3,'유모차/웨건'),
(3,'카시트'),(3,'아기띠/외출용품'),(3,'욕실용품/스킨케어'),(3,'위생/건강/세제'),
(3,'유아동침구'),(3,'유아가구/인테리어'),(3,'완구/교구');
INSERT INTO coupang.category_info
(cate_parent_seq, cate_name)
VALUES
(4,'유기농/친환경 전문관'),(4,'과일'),(4,'견과/건과'),(4,'채소'),(4,'쌀/잡곡'),
(4,'축산/계란'),(4,'수산물/건어물'),(4,'생수/음료'),(4,'커피/원두/차'),(4,'과자/초콜릿/시리얼'),
(4,'면/통조림/가공식품'),(4,'가루/조미료/오일'),(4,'장/소스/드레싱/식초'),
(4,'유제품/아이스크림'),(4,'냉장/냉동/간편요리'),(4,'건강식품'),
(5,'주방가전'),(5,'냄비/프라이팬'),(5,'주방조리도구'),(5,'그릇/홈세트'),(5,'수저/커트러리'),
(5,'컵/텀블러/와인용품'),(5,'주전자/커피/티용품'),(5,'수방수납/정리'),(5,'밀폐저장/도시락'),
(5,'주방잡화'),(5,'일회용품/종이컵'),(5,'보온/보냉용품'),(5,'수입주방'),(5,'1인가구 주방용품'),
(6,'방한용품'),(6,'헤어'),(6,'바디/세안'),(6,'구강/면도'),(6,'화장지/물티슈'),(6,'생리대/성인기저귀'),
(6,'기저귀'),(6,'세탁세제'),(6,'청소/주방세제'),(6,'탈취/방향/살충'),(6,'건강/의료용품'),
(6,'성인용품(19)'),(6,'세탁/청소용품'),(6,'욕실용품'),(6,'생활전기용품'),(6,'수납/정리'),
(7,'크리스마스용품'),(7,'방한용품'),(7,'F/W 침구샵'),(7,'싱글하우스'),(7,'홈데코'),
(7,'가구'),(7,'수납/정리'),(7,'침구'),(7,'커튼/블라인드'),(7,'카페트/쿠션/거실화'),
(7,'수예/수선'),(7,'욕실용품'),(7,'조명/스탠드'),(7,'셀프인테리어'),(7,'원예/가드닝'),
(8,'TV/영상가전'),(8,'냉장고'),(8,'세탁기/건조기'),(8,'생활가전'),(8,'청소기'),(8,'계절가전'),
(8,'뷰티/헤어가전'),(8,'건강가전'),(8,'주방가전'),(8,'노트북'),(8,'데스크탑'),(8,'모니터'),
(8,'휴대폰'),(8,'태블릿PC'),(8,'스마트워치/밴드'),(8,'게임');
INSERT INTO coupang.manufacturer_info
(manu_name)
VALUES('레노버');
select * from manufacturer_info where manu_name like '%레노%';
select * from category_info where cate_name like '%노트%';
INSERT INTO coupang.product_basic_info
(pbi_name, pbi_price, pbi_discounted_price, pbi_wow_discounted_price, pbi_status, pbi_manu_seq, pbi_si_seq, pbi_cate_seq)
VALUES('레노버 2021 V14 GEN2 14', 449000, null , null, 0, 12, 1, 179);
select * from product_basic_info;
INSERT INTO coupang.product_brief_info
(pbri_pbi_seq, pbri_content, pbri_order)
VALUES
(11, 'CPU 모델 번호: 라이젠5-5500U', 1),
(11, 'GPU 모델명: AMD 라데온 그래픽스', 2),
(11, '화면크기: 35.56cm', 3),
(11, '그래픽카드 형태: 미장착', 4),
(11, '해상도 (해상도 등급): FHD', 5),
(11, '쿠팡상품번호: 5320085643 - 13564560035', 6);
INSERT INTO coupang.product_detail_info
(pdi_content, pdi_type, pdi_order, pdi_pbi_seq)
VALUES('Lenovo.jpg', 1, 1, 11);
INSERT INTO coupang.seller_info
(si_name, si_id, si_pwd, si_address, si_phone, si_business_num)
VALUES('', '', '', '', '', '');
INSERT INTO coupang.mandatory_notice
(mn_manu_seq, mn_prod_dt, mn_origin, mn_capacity, mn_counsel_num, mn_caution, mn_pbi_seq,mn_importer)
VALUES(12, '2021-02-00', '중국' , null , '쿠팡고객센터 1577-7011', null, 11,'LCFC (HeFei) Electronics Technology Co., Ltd. / 한국레노버 유한회사');
INSERT INTO coupang.manadatory_food_detail_info
(mfdi_import_phase, mfdi_prod_comp, mfdi_expiration_dt, mfdi_detailed_item_note, mfdi_pbi_seq)
VALUES(1, null, '2023-05-05', null, 8);
INSERT INTO coupang.mandatory_electronics_detail_info
(medi_warranty_standard, medi_voltage_power, medi_energy_rating, medi_extra_fee, medi_prod_comp, medi_prod_size, medi_prod_area, medi_pbi_seq)
VALUES('제품 이상 시 공정거래위원회 고시 소비자분쟁해결기준에 의거 보상합니다.', '220V, 38Wh' , '해당없음', null , null, null, null , 11);
INSERT INTO coupang.point_info
(pi_pbi_seq, pi_member_type, pi_point)
VALUES(11, 0, 4490),(11,1,22450);
INSERT INTO coupang.certification_info
(ci_name, ci_content)
VALUES('KC인증 전기용품 안전확인', '전파환경 및 방송통신망 등에 위해를 줄 우려가 있는 기자재와 중대한 전자파장해를 주거나 전자파로부터 정상적인 동작을 방해받을 정도의 영향을 받는 기자재에 대한 기술수준 및 전자파 인체보호기준 등에 대한 적합 여부 평가');
update mandatory_notice set mn_importer = 'Guangdong Fuxin Electronic Technology Limited Company/범용전자' where mn_pbi_seq = 9;
INSERT INTO coupang.product_certification
(pc_num, pc_pbi_seq, pc_ci_seq)
VALUES('MSIP-REM-PPQ-ADLX65CLGR2A', 11, 3);
select * from product_buy_option_category;
INSERT INTO coupang.product_buy_option
(pbo_pboc_seq, pbo_title, pbo_price)
VALUES
(4, '라이젠5 4세대 × 256GB × 8GB × Free DOS × 82KC0011KR', 0),
(4, '라이젠5 5세대 × 256GB × 4GB × Free DOS × 82KC0011KR', -20000),
(4, '라이젠5 5세대 × 256GB × 4GB × WIN10 Home × 82KC0013KR', 90000),
(4, '라이젠7 4세대 × 256GB × 8GB × Free DOS × 82KC0012KR', 584540);
INSERT INTO coupang.product_buy_option_category
(pboc_pbi_seq, pboc_title)
VALUES(11, 'CPU모델명 x 저장용량 x RAM용량 x 운영체제 x 모델명/품번');Select Sql문
select * from product_certification;
-- select 구문 : 상품명 검색, 판매자 검색, 카테고리 선택
-- 가격 범위, 날짜범위, 총 카운트, 페이지 카운트,
-- 페이징 처리 (limit, offset), 정렬(가격 오름차순/내림차순)
-- view 필요하다싶으면 생성하기
-- 제품 간략 정보
select * from product_basic_info a inner join product_brief_info b
on a.pbi_seq = b.pbri_pbi_seq
join seller_info c
on a.pbi_si_seq = c.si_seq
where a.pbi_name like '%%' and c.si_name like '%%'
order by pbi_seq desc
limit 10 offset (1-1)*10;
-- 검색 (상품명)
select a.* , b.cate_name from product_basic_info a inner join category_info b
on a.pbi_cate_seq = b.cate_seq
where pbi_name like '%%';
-- 검색 (날짜 범위)
select * from review_info where
ri_reg_dt between '2020-05-05' and '2020-10-01';
-- 검색 (카테고리)
select * from product_basic_info where pbi_cate_seq = 4;
-- 검색 (가격범위)
select * from product_basic_info where pbi_price >=5000 and pbi_price <=10000;
-- 또는
select * from product_basic_info where pbi_price between 5000 and 10000;
-- 검색 결과 수 (총 개수)
select count(*) as totalCnt from product_basic_info pbi
where pbi_name like '%%';
-- 총 페이지 수
select ceil(count(*)/5) as pageCnt from product_basic_info; -- 올림 사용
-- 페이징 처리 + 검색
select * from product_basic_info
order by pbi_seq desc -- 최근 등록순으로
limit 5 offset 0; -- (offset = (page-1)*5)
-- 가격 내림차수 정렬
select * from product_basic_info
order by pbi_price desc;
-- 가격 오름차수 정렬
select * from product_basic_info
order by pbi_price;
-- 할인율, 와우 할인율 출력
select round((1-(pbi_discounted_price/pbi_price))*100) as discount_rate ,
round((1-(pbi_wow_discounted_price/pbi_price))*100) as wow_discount_rate
from product_basic_info;
-- 상세페이지 정보출력 뷰 생성
create view product_detail_page_show as
select a.pbi_name as name, a.pbi_price as price, -- 제품이름, 가격
round((1-(pbi_discounted_price/pbi_price))*100) as discount_rate ,-- 할인율
round((1-(pbi_wow_discounted_price/pbi_price))*100) as wow_discount_rate,-- 와우 할인가
a.pbi_discounted_price as discounted_price , -- 할인가
b.pi_point as point, d.pboc_title as option_name, -- 적립포인트, 옵션이름
e.pbo_title as option_choice, (a.pbi_discounted_price + e.pbo_price) as option_price, c.pbri_content as brief_explain, -- 옵션 선택지, 옵션가, 간략설명 (순서대로)
adddate(now(),interval f.di_arrival day)as delivery_arrive_dt, -- 배송완료 예정일
dc.dc_name as delivery_company ,b.pi_member_type
from product_basic_info a left outer join point_info b
on a.pbi_seq = b.pi_pbi_seq
inner join product_brief_info c
on a.pbi_seq = c.pbri_pbi_seq
left outer join product_buy_option_category d
on a.pbi_seq = d.pboc_pbi_seq
left outer join product_buy_option e
on d.pboc_seq = e.pbo_pboc_seq
left outer join delivery_info f
on a.pbi_seq = f.di_pbi_seq
left outer join delivery_company dc
on f.di_dc_seq = dc.dc_seq
order by a.pbi_seq, b.pi_member_type, e.pbo_title, c.pbri_order;
-- 제품순서대로 정렬 후,
-- 일반멤버에게 보여줄 정보 출력 후 와우 멤버에게 보여줄 정보 출력
-- 옵션종류에 따라 정렬
-- 간략 설명 순서에 따라 정렬
select * from product_detail_page_show;
-- 상품목록페이지 조회 뷰 생성
create view product_list_page_show as
select b.di_price , a.pbi_name, a.pbi_price, -- 배송비용, 상품이름, 가격,
round((1-(pbi_discounted_price/pbi_price))*100) as discount_rate , -- 할인율
a.pbi_discounted_price as discounted_price, b.di_type as delivery_type,-- 할인된 가격, 배송종류
adddate(now(),interval b.di_arrival day)as delivery_arrive_dt,-- 배송완료 예정일
c.pi_point as points
from product_basic_info a join delivery_info b
on a.pbi_seq = b.di_pbi_seq
left outer join point_info c
on a.pbi_seq = c.pi_pbi_seq ;
-- 각제품별 최대 포인트데이터만 출력
select * from product_list_page_show group by pbi_name;
-- 제품 qna 질문 출력
select b.mi_name as id, a.pq_reg_dt as dt, pbo.pbo_title as option_name, -- 질문자 아이디, 날짜, 선택한 옵션
d.si_name, a.pq_content as content -- 판매자, 질문내용
from product_qna a join member_info b
on a.pq_mi_seq = b.mi_seq
join product_basic_info c
on c.pbi_seq = a.pq_pbi_seq
join seller_info d
on c.pbi_si_seq = d.si_seq
left outer join wish_list e
on e.wl_pbi_seq = c.pbi_seq
left outer join order_status os
on os.os_mi_seq = b.mi_seq
left outer join product_buy_option pbo
on pbo.pbo_seq = e.wl_pbo_seq
left outer join product_qna_answer pqa
on pqa.pqa_pq_seq = a.pq_seq ;
-- 제품 qna 답변 출력
select b.si_name, a.pqa_reg_dt, a.pqa_content
from product_qna_answer a
left outer join seller_info b
on a.pqa_si_seq = b.si_seq ;
-- 제품 qna 질문과 답변 동시출력 뷰 생성
create view product_qna_and_answer as
select b.mi_name as id, a.pq_reg_dt as dt, pbo.pbo_title as option_name, -- 질문자 아이디, 날짜, 선택한 옵션
d.si_name, a.pq_content as content, -- 판매자, 질문내용
d.si_name as answer_writer, pqa.pqa_reg_dt , pqa.pqa_content as qna_answer -- 판매자, 답변 날짜, 답변내용
from product_qna a join member_info b
on a.pq_mi_seq = b.mi_seq
join product_basic_info c
on c.pbi_seq = a.pq_pbi_seq
join seller_info d
on c.pbi_si_seq = d.si_seq
left outer join wish_list e
on e.wl_pbi_seq = c.pbi_seq
left outer join order_status os
on os.os_mi_seq = b.mi_seq
left outer join product_buy_option pbo
on pbo.pbo_seq = e.wl_pbo_seq
left outer join product_qna_answer pqa
on pqa.pqa_pq_seq = a.pq_seq
order by a.pq_seq;
select * from product_qna_and_answer;
-- 리뷰 카테고리별 리뷰
select c.rbi_name, d.rbic_name, count(e.rbicc_seq) as cnt, b.pbi_name
from review_info a join product_basic_info b
on a.ri_pbi_seq = b.pbi_seq
left outer join review_by_item c
on c.rbi_pbi_seq = b.pbi_seq
left outer join review_by_item_choice d
on d.rbic_rbi_seq = c.rbi_seq
left outer join review_by_item_customer_choice e
on e.rbicc_rbic_seq = d.rbic_seq
where d.rbic_seq = 1 -- 카테고리 번호에 따라 몇명이 선택했는지 출력
group by d.rbic_name;
select *, count(*) from review_by_item_choice group by rbic_name;
-- 리뷰 출력
select mi.mi_name , a.ri_score , a.ri_reg_dt , si.si_name
, a.ri_content , b.pbi_name , d.*
from review_info a join product_basic_info b
on a.ri_pbi_seq = b.pbi_seq
join review_by_item c
on c.rbi_pbi_seq = b.pbi_seq
join review_by_item_choice d
on d.rbic_rbi_seq = c.rbi_seq
join review_by_item_customer_choice e
on e.rbicc_rbic_seq = a.ri_mi_seq
join member_info mi
on mi.mi_seq = a.ri_mi_seq
join seller_info si
on si.si_seq = b.pbi_si_seq ;
where a.ri_mi_seq = e.rbicc_mi_seq ;
chobo