본문 바로가기

Database/MSSQL work

join

 

--cross join

select * from salesTbl cross join goodsTbl

 

 

--inner join

select
* 
from salesTbl, goodsTbl
where salesTbl.goodsId = goodsTbl.goodsId


select
* 
from salesTbl inner join goodsTbl
ON salesTbl.goodsId = goodsTbl.goodsId

 

 

 

--고객이 언제 어떤 상품을 얼만큼 사갔다

select custId, saleDate, salesTbl.goodsId, goodsName, sales
from salesTbl, goodsTbl


select custId, saleDate, salesTbl.goodsId, goodsName, sales
from salesTbl inner join goodsTbl
ON salesTbl.goodsId = goodsTbl.goodsId

 

 

 

-- S, G 만들기

select custId, saleDate, S.goodsId, goodsName, sales
from salesTbl S, goodsTbl G
where S.goodsId = G.goodsId


select custId, saleDate, S.goodsId, goodsName, sales
from salesTbl S inner join goodsTbl G
ON S.goodsId = G.goodsId

 

 

 

-- 조건달기

select custId, saleDate, S.goodsId, goodsName, sales
from salesTbl S, goodsTbl G
where S.goodsId = G.goodsId
AND sales >= 5000


select custId, saleDate, S.goodsId, goodsName, sales
from salesTbl S inner join goodsTbl G
ON S.goodsId = G.goodsId
WHERE sales >= 5000

 

 

--판매테이블과 고객테이블을 inner join

select *
from salesTbl S inner join customersTbl C
On S.custId = C.custId;

 

--위와 동일, inner join은 defalut로 생략이 가능하다

select *
from salesTbl S 
inner join customersTbl C On S.custId = C.custId;

 

 

--2018-09-02에 구매한 고객정보 및 상품정보 출력
--고객ID, 고객명, 주소, 상품코드, 수량, 판매일

select C.custId, C.custName, C.addr, S.goodsId, S.quantity, S.saleDate
from salesTbl S 
inner join customersTbl C On S.custId = C.custId
where saleDate = '2018-09-02';

 

 

--김철수가 구매한 이력 조회

select C.custId, C.custName, C.addr, S.goodsId, S.quantity, S.saleDate
from salesTbl S 
inner join customersTbl C On S.custId = C.custId
where custName='김철수';

 

 

 

 

 

--2018-09-02에 구매한 고객정보 및 상품정보 출력
--고객ID, 고객명, 주소, 상품명, 수량, 판매일

select C.custId, C.custName, C.addr, G.goodsName, S.quantity, S.saleDate
from salesTbl S 
inner join customersTbl C On S.custId = C.custId
inner join goodsTbl G On S.goodsId = G.goodsId
where saleDate = '2018-09-02';

 

 

 

 

--철수가 구매한거 목록보기

select C.custId, C.custName, C.addr, G.goodsName, S.quantity, S.saleDate
from salesTbl S 
inner join customersTbl C On S.custId = C.custId
inner join goodsTbl G On S.goodsId = G.goodsId
where C.custName='김철수'

 

 

 

 

--판매테이블의 고객아이디 갯수를 출력

select count(distinct S.custId)
from salesTbl S
inner join customersTbl C ON S.custId = C.custId;

--위와 동일
select count(distinct custId) from salesTbl;

 

 

 

 

 

 

 

--right outer join

select C.custId, C.custName, C.addr, S.goodsId, S.quantity, S.saleDate
from salesTbl S, customersTbl C
where S.custId = C.custId

--위와 동일
select C.custId, C.custName, C.addr, S.goodsId, S.quantity, S.saleDate
from salesTbl s right /*outer*/ join customersTbl C
On S.custId = C.custId

 

 

 

 

 

--고객이름, 고객이 구매한 횟수

select c.custName, count(sales)
from salesTbl s right join customersTbl c
On s.custId = c.custId
group by c.custName

 

 

--세개의 테이블을 조인하여 구매내역 테이블에서 정보를 뽑고,
--한번도 판매되지 않은 상품도 출력하라

select C.custId, C.custName, C.addr, S.quantity, S.saleDate, G.goodsName
from salesTbl S 
inner join customersTbl C On S.custId = C.custId
right join goodsTbl G On S.goodsId = G.goodsId

 

 

 

--한번도 구매한 적 없는 고객이름은 출력, 판매되지않은 상품은 출력되지 않도록 해라

select C.custId, C.custName, C.addr, S.quantity, S.saleDate, G.goodsName
from salesTbl S 
full outer join customersTbl C On S.custId = C.custId
left join goodsTbl G On S.goodsId = G.goodsId

 

 

 

 

--위와 동일

select C.custId, C.custName, C.addr, S.quantity, S.saleDate, G.goodsName
from salesTbl S 
right outer join customersTbl C On S.custId = C.custId
left outer join goodsTbl G On S.goodsId = G.goodsId

 

 

 

 

--위에 판매된 적 없는 상품도 추가

select C.custId, C.custName, C.addr, S.quantity, S.saleDate, G.goodsName
from salesTbl S 
right outer join customersTbl C On S.custId = C.custId
full outer join goodsTbl G On S.goodsId = G.goodsId

 

 

--고객 아이디 , 고객이름, 그사람의 총 구매액을 높을순서부터 출력

select C.custId, C.custName, ISNULL( sum(sales), 0 ) as '총 구매액'
from customersTbl C
left join salesTbl S ON C.custId = S.custId
group by C.custId, C.custName
order by sum(sales) desc

 

 

 

 

 

--고객 등급 if문으로 출력
--만원이상 A, 5천원이상 B, 10원이상 C, 구매안한고객 D


select C.custId, C.custName, ISNULL( sum(sales), 0 ) as '총 구매액',

	case when sum(sales) >= 10000 then 'A'
		 when sum(sales) >= 5000 then 'B'
		 when sum(sales) >= 10 then 'C'
		 else 'D'
	end as[고객등급]

from customersTbl C
left join salesTbl S ON C.custId = S.custId
group by C.custId, C.custName
order by sum(sales) desc

 

 

 

--위의 내용을 이용하여 컬럼의 update하기 

select *from customersTbl


update customersTbl
set 
customersTbl.grade = S.grade2
from customersTbl as C,
	(
		select C.custId,
			case when sum(sales) >= 10000 then 'A'
				 when sum(sales) >= 5000 then 'B'
				 when sum(sales) >= 10 then 'C'
				 else 'D'
			end as grade2

		from customersTbl C
		left join salesTbl S ON C.custId = S.custId
		group by c.custId
	)
	as S
where c.custId = s.custId

'Database > MSSQL work' 카테고리의 다른 글

다른테이블을 내가 등급나눠 업뎃하기  (0) 2020.11.11
union, union all, intersect, except  (0) 2020.11.10
if문, case문  (0) 2020.11.10
NULL 값을 0으로 표시  (0) 2020.11.10
Rollup(), Grouping  (0) 2020.11.09