회원랭킹 & 급상승 회원랭킹

회원랭킹
이름 : mRank(int, int, int)
기능 : 선택된 카페에 등록된 회원의 총 접속수 별 랭킹
설명 : 선택된 카페에 등록된 모든 회원의 총 접속수를 더하여 1위 ~ 50위까지의 순위를 출력한다.
연결테이블 : member_join, member

create proc mRank(@pagein int, @page int, @cafeNum int)
as
begin
  declare @tempTable table
 (
  rank int,
  mName nvarchar(5),
  mID varchar(20),
  mVisit int,
  mDate datetime
 )

 insert into @tempTable
 select rank() over(order by visit_count desc) as rank,
 name as mName, id as mID, visit_count as mVisit, join_date as mDate
 from member_join as mj
 inner join member as mb
 on mj.fk_member = mb.sn
 where fk_cafe = @cafeNum

 select top(@pagein) rank, mName, mID, mVisit, mDate from @tempTable
 where rank not in (select top((@page-1)*@pagein) rank from @tempTable)

end

--하위쿼리
declare @cafeNum int
set @cafeNum = 1

select top(50)
rank() over(order by visit_count desc) as rank,
(select name from member where sn = fk_member) as mName, visit_count
from member_join
where fk_cafe = @cafeNum

급상승 회원랭킹
이름 : mRankUp(int, int, int)
기능 : 선택된 카페에 등록된 회원의 30일간의 접속 랭킹
설명 : 선택된 카페에 등록된 모든 회원의 최근 30일 간의 총 접속수 를 더하여 1위 ~ 50위까지의 순위를 출력한다.
연결테이블 : member_visit, member_join, member
memName() 함수를 이용하여 이름을 출력한다.

create proc mRankUp(@cafeNum int, @page int, @pagein int)
as
begin

 --검색된 데이터를 담을 테이블을 하나 만든다.
 declare @tempTable table
 (
  rank int,
  mName nvarchar(5),
  mID varchar(20),
  cnt int,
  join_date datetime
 )

 --select된 데이터를 담는다.
 insert into @tempTable
 select rank() over (order by mVisit desc) rank, mName, mID, mVisit, join_date from
 (
  select mb.name as mName, mb.id as mID, sum(visit_count_day) as mVisit, mv.fk_member as fk_member from member_visit as mv
  inner join member as mb
  on mv.fk_member = mb.sn
  where mv.fk_cafe = @cafeNum
  and cast(mv.visit_date_day as datetime) >= datediff(dd, getdate(), -30)
  group by mv.fk_member, mb.name, mb.id
 ) as mv_mb
 inner join member_join as mj
 on mv_mb.fk_member = mj.fk_member
 where fk_cafe = @cafeNum

 select top(@pagein) rank, mName, mID, cnt, join_date from @tempTable
 where rank not in (select top((@page-1)*@pagein) rank from @tempTable order by rank asc)
 order by rank asc

end