[MySQL] movies + 피벗 테이블 만들기

create database moviedb;

use moviedb;
create table movietbl
(movie_id INT,
movie_title VARCHAR(30),
movie_director VARCHAR(20),
movie_start VARCHAR(20),
movie_script LONGTEXT,
movie_film LONGBLOB
) DEFAULT CHARSET=utf8mb4;





use moviedb;


insert into movietbl 
values (1, '쉰들러 리스트', '스필버그', '리암 니슨', load_file('c:/SQL/Movies/Schindler.txt'), load_file('c:/SQL/Movies/Schindler.mp4'));

insert into movietbl 
values (2, '쇼생크 탈출', '프랭크 다라본트', '팀 로빈스', load_file('c:/SQL/Movies/Shawshank.txt'), load_file('c:/SQL/Movies/Shawshank.mp4'));

insert into movietbl 
values (3, '라스트 모히칸', '마이클 만', '다니엘 데이 루이스', load_file('c:/SQL/Movies/Mohican.txt'), load_file('c:/SQL/Movies/Mohican.mp4'));

select * from movietbl;

select movie_script from movietbl where movie_id=1
into outfile 'c:/SQL/Movies/Schindler_out.xt'
LINES TERMINATED BY '\\n';

select movie_film from movietbl where movie_id=3
into DUMPFILE 'c:/SQL/Movies/Mohican_out.mp4';

 

 

+++

 

use sqldb;
create table pivotTest
( uName CHAR(3),
season CHAR(2),
amount INT);


INSERT INTO pivotTest VALUES
('김범수', '겨울', 10),
('윤종신', '여름', 15),
('김범수', '가을', 25),
('김범수', '봄', 3),
('김범수', '봄', 37),
('윤종신', '겨울', 40),
('김범수', '여름', 14),
('김범수', '겨울', 22),
('윤종신', '여름', 64);

select * from pivotTest;

select uName,
SUM(if(season='봄', amount , 0)) AS '봄',
SUM(if(season='여름', amount , 0)) AS '여름',
SUM(if(season='가을', amount , 0)) AS '가을',
SUM(if(season='겨울', amount , 0)) AS '겨울',
sum(amount) as '합계' from pivotTest GROUP BY uName;

SELECT season,
    SUM(IF(uName='김범수', amount, 0)) AS '김범수',
    SUM(IF(uName='윤종신', amount, 0)) AS '윤종신',
    SUM(amount) AS '합계'
FROM pivotTest
GROUP BY season
ORDER BY season
LIMIT 0, 1000;