VIEW 실습

1.
회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여
회원아이디와 이메일을 포함한 뷰테이블을 생성하시오.

 

CREATE view v_max_amt_member
as
SELECT
	m.m_id								AS m_id
	,m.m_email							AS m_email
	,g.g_name							AS m_name
	,MAX(g.g_price* o.o_amount)	AS m_max_amt
FROM
	tb_order AS o
	INNER join
	tb_goods AS g
	on
	o.o_g_code = g.g_code
	INNER join
	tb_member AS m
	ON 
	m.m_id = o.o_id
GROUP BY m.m_id;​