주말복습6 (DB)

주말에 csv파일을 하나 받았다.

HeidiSQL에서  

create table product (
 no int AUTO_INCREMENT primary key, #번호 (기본키)
 bcode int , #지점코드
 month int, #월
 state int,#상태 1.생산 2.판매 3.반품
 car varchar(30), #차명
 qty int, #수량
 remark varchar(100) #반품사유
 )

다음과같이 테이블을 먼저 생성해줬음

 

product.csv

0.25MB

 

위 csv파일을 이용해  데이터를 넣을거다

 

 

HeidiSQL에 들어가 상단의 도구 ->csv파일 가져오기 클릭

가져오기가 성공적으로됐다면

이 테이블을 이용해  여러 쿼리를 써보자

-- 월별 판매갯수  조회해보기
SELECT MONTH , COUNT(*) 판매갯수
FROM product 
WHERE state=2  #상태 2 : 판매
GROUP BY MONTH;

---

#(2) 월별 생산,판매, 반품 합계 현황
SELECT MONTH , COUNT(*)'총 계',
COUNT(if(state = 1 ,1,NULL)) '생산',
COUNT(if(state = 2 ,1,NULL)) '판매',
COUNT(if(state = 3 ,1,NULL)) '반품'
FROM product p
GROUP BY MONTH;

---

 

#(3) 제품별 총 생산, 판매, 반품 현황
SELECT car,COUNT(*) 계 , 
SUM(if(state=1 , 1, 0)) 생산, 
# if을 이용해 참:1 , 거짓: 0으로반환함
# sum으로 숫자를 모두 더함 
SUM(if(state=2 , 1, 0))판매,
SUM(if(state=3 , 1, 0)) 반품
FROM product 
GROUP BY car;#제품을 그룹으로 나눔

---

#(5) 월별 제품별 생산, 판매, 반품 현황을 조회하는데,
# 월별 생산+판매+반품 합계도 출력
SELECT MONTH , car,COUNT(*) 계 , 
SUM(if(state=1 , 1, 0)) 생산, 
# if을 이용해 참:1 , 거짓: 0으로반환함
# sum으로 숫자를 모두 더함 
SUM(if(state=2 , 1, 0))판매,
SUM(if(state=3 , 1, 0)) 반품
FROM product 
GROUP BY MONTH , car with ROLLUP; 
# with rollup으로 월별 합계를 출력

 

---

 

#(6)월별 생산, 판매 , 반품 조회해보자
SELECT if(state=1 , '생산', 
		if(state = 2,'판매','반품')) 상태,
		SUM(if(MONTH=1,1,0)) 1월,
		SUM(if(MONTH=2,1,0)) 2월,
		SUM(if(MONTH=3,1,0)) 3월,
		SUM(if(MONTH=4,1,0)) 4월,
		SUM(if(MONTH=5,1,0)) 5월,
		SUM(if(MONTH=6,1,0)) 6월,
		SUM(if(MONTH=7,1,0)) 7월,
		SUM(if(MONTH=8,1,0)) 8월,
		SUM(if(MONTH=9,1,0)) 9월,
		SUM(if(MONTH=10,1,0)) 10월,
		SUM(if(MONTH=11,1,0)) 11월,
		SUM(if(MONTH=12,1,0)) 12월
FROM product 
GROUP BY state;

---

#(7)자동차별 ,상태별  월별 생산, 판매 , 반품 조회해보자
# 자동차의 월별 생산+판매+반품도  출력하기
SELECT car,if(state=1 , '생산', 
		if(state = 2,'판매',
		if(state=3, '반품','(계)'))) 상태,
		SUM(if(MONTH=1,1,0)) 1월,
		SUM(if(MONTH=2,1,0)) 2월,
		SUM(if(MONTH=3,1,0)) 3월,
		SUM(if(MONTH=4,1,0)) 4월,
		SUM(if(MONTH=5,1,0)) 5월,
		SUM(if(MONTH=6,1,0)) 6월,
		SUM(if(MONTH=7,1,0)) 7월,
		SUM(if(MONTH=8,1,0)) 8월,
		SUM(if(MONTH=9,1,0)) 9월,
		SUM(if(MONTH=10,1,0)) 10월,
		SUM(if(MONTH=11,1,0)) 11월,
		SUM(if(MONTH=12,1,0)) 12월
FROM product 
GROUP BY car , state WITH ROLLUP;
#차로 먼저그룹을 만든 후 각 차의 상태로 그룹을 만듬

 

---

# 지점(bcode)의 판매순위를 매겨보자
SELECT bcode ,COUNT(*), RANK()OVER(ORDER BY COUNT(*) desc) 판매순위
			#count(*)의 내림차순으로 순위를 정해줌
FROM product
WHERE state=2 # (판매)만
GROUP BY bcode
order BY COUNT(*) DESC;

 

---

# 월 별 , 각 지점의 판매갯수 
SELECT  MONTH , COUNT(*) 총계 , 
COUNT(if(bcode = 1, 1 , NULL)) 지점1,
COUNT(if(bcode = 2, 1 , NULL)) 지점2,
COUNT(if(bcode = 3, 1 , NULL)) 지점3,
COUNT(if(bcode = 4, 1 , NULL)) 지점4,
COUNT(if(bcode = 5, 1 , NULL)) 지점5
FROM product 
WHERE state=2
GROUP BY MONTH ;