인기 카테고리

이름 : bestCate
기능 : 인기 카테고리를 순위대로 출력한다.
설명 : 조회수를 기준으로 인기 카테고리를 출력하며 총 조회수, 회원수, 카테고리이름을 함께 출력한다.
반환

create proc bestCate
as
begin
select cg.cate_name, sum(cafe_manage.dbo.nullNum(mCnt)) as mCnt, sum(cafe_manage.dbo.nullNum(mVisit)) as mVisit from
(
 select fk_cafe as cafeNum, count(*) as mCnt from member_join
 where join_date >= datediff(dd, getdate(), -7)
 group by fk_cafe
) as mj
inner join
(
 select fk_cafe as cafeNum, sum(visit_count_day) as mVisit
 from member_visit
 where cast(visit_date_day as datetime) >= datediff(dd, getdate(), -7)
 group by fk_cafe
) as mv
on mj.cafeNum = mv.cafeNum
right join cafe as cf
on mj.cafeNum = cf.sn
right join category cg
on cf.fk_cate = cg.cate_id
group by fk_cate, cg.cate_name
order by mVisit desc, cate_name asc
end

------------------------------------------------------------------------
create proc bestCate
as
begin
select cate_name, tcnt, cnt from
(
 select sum(tcnt) as tcnt, sum(cnt) as cnt, fk_cate from
 (
  select fk_cafe,sum(visit_count) as tcnt, count(*) as cnt from member_join
  group by fk_cafe
 ) mj1
 inner join cafe as cf
 on mj1.fk_cafe = cf.sn
 group by fk_cate
) mj2
inner join Category as ct
on mj2.fk_cate = ct.cate_id
order by tcnt desc
end

-- 하위쿼리 실행
select (select cate_name from Category where cate_id = fk_cate) as name, tcnt, cnt from
(
 select sum(tcnt) as tcnt, sum(cnt) as cnt, fk_cate from
 (
  select fk_cafe,sum(visit_count) as tcnt, count(*) as cnt from member_join
  group by fk_cafe
 ) mJoin
 inner join cafe
 on mJoin.fk_cafe = cafe.sn
 group by fk_cate
) mJoin
order by tcnt desc