[Mysql] 데이터 join, sum 예제

-korean 테이블

-history 테이블2

[1번]

방정환님의 데이터를
name, id, tel1, email, price의 총합계, service 출력
단, 이름, 아이디,전화번호1,이메일, 총구매가격, 통신사 로 출력

 

select korean.name as 이름, history.id as 아이디, korean.tel1 as 전화번호1, korean.email as 이메일,sum(history.goodsprice) as 총합계,korean.service as 통신사  from korean left join history on korean.id=history.id where korean.name='방정환';

 

select korean.name as 이름, history.id as 아이디, korean.tel1 as 전화번호1, korean.email as 이메일,sum(history.goodsprice) as 총합계,korean.service as 통신사  from korean left join history on korean.id=history.id where korean.name='방정환';

select name  이름, history.id  아이디, tel1  전화번호1, email 이메일,sum(goodsprice) 총합계,service 통신사 from korean left join history on korean.id=history.id where korean.name='방정환';

 

select name  이름, history.id  아이디, tel1  전화번호1, email 이메일,sum(goodsprice) 총합계,service 통신사 from korean left join history on korean.id=history.id where korean.name='방정환';

[2번]
skt쓰는 사람 중 gmail.com 사용중인 사람 출력
select * from korean where email like '%@gmail.com' and service='SKT';

select * from korean where email like '%@gmail.com' and service='SKT';

[3번]
아이템 별로 구매개수 확인
select  goodsname, count(*)as 아이템 from history group by goodsname;

select  goodsname, count(*)as 아이템 from history group by goodsname;

[4번]
1900년도 이후에 태어난 사람중 바미의불꽃을 구매하고 전화번호2가 있는 사람 중 구매내역이 22년 5월 이후인 사람 출력
select name,korean.id,years, tel2,goodsname,buytime from korean left join history on korean.id=history.id where years>1900 and goodsname='바미의불꽃'and korean.tel2 like '%'and buytime>'2022-05-01';

select name,korean.id,years, tel2,goodsname,buytime from korean left join history on korean.id=history.id where years>1900 and goodsname='바미의불꽃'and korean.tel2 like '%'and buytime>'2022-05-01';

[5번]
2022년 3월 부터 5월 중 3500원 이하 구매자에서 전화번호2가 있고 @nate.com 사용자 중 1900년 이전 출생자

select name, buytime, goodsprice,tel2,email,years from korean left join history on korean.id=history.id where buytime > '2022-03-01' and buytime < '2022-05-31' and goodsprice<3500 and  tel2 like '0%'and email like '%@nate.com' and years<1900;

select name, buytime, goodsprice,tel2,email,years from korean left join history on korean.id=history.id where buytime > '2022-03-01' and buytime < '2022-05-31' and goodsprice<3500 and  tel2 like '0%'and email like '%@nate.com' and years<1900;

[6번]
nate.com 을 사용하고 3천원이상 아이템 구매한사람 아이템가격 오름차순으로설정

 

select name, email,goodsprice from korean left join history on korean.id=history.id where email like '%@nate.com' and goodsprice >=3000  order by goodsprice asc;

select name, email,goodsprice from korean left join history on korean.id=history.id where email like '%@nate.com' and goodsprice >=3000  order by goodsprice asc;

방황했던 이유: join history on 을 join on history 로 작성