090425 현재까지 작성된 쿼리모음

cafeManage_090425.zip

--회원랭킹
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
go

--급상승회원랭킹
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
go

--인기카테고리
create proc cafeTrend(@cafeNum int)
as
begin
 declare @tempTable table
 (
  dataGroup char(10)
 )

 declare @i int
 set @i = 7
 while(@i >= 0)
 begin
  insert into @tempTable
  select convert(char(10), getdate() - @i, 120)
  set @i = @i - 1
 end

 select dataGroup, isnull(num, 0) as num, isnull(cnt, 0) as cnt from
 (
 select count(fk_member) as num, cnt, visit_date_day from
 (
 SELECT SUM(visit_count_day) AS cnt, visit_date_day as visit_date_day FROM member_visit
 WHERE fk_cafe = @cafeNum and
 cast(visit_date_day as datetime) between getdate()-7 and getdate()
 GROUP BY visit_date_day
 ) as jj
 inner join member_join as mj
 on jj.visit_date_day = convert(char(10), mj.join_date, 120)
 where fk_cafe = @cafeNum
 group by visit_date_day, cnt
 ) as jj
 right outer join @tempTable as tmp
 on jj.visit_date_day = tmp.dataGroup

end
go

--인기카테고리
create proc bestCate
as
begin
 select cg.cate_name, sum(isnull(mCnt, 0)) as mCnt, sum(isnull(mVisit, 0)) 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
go

--카페명 및 해당 카페 가입된 회원여부 출력
create proc cName(@cNum int, @mID varchar(20))
as
begin
 select name as cName,
 isnull((
 select count(*) from member_join as mj
 inner join member as mb
 on mj.fk_member = mb.sn
 where mj.fk_cafe = 1
 group by mb.id
 having mb.id=@mID), 0) as cnt
 from cafe
 where sn = @cNum
end
go

-- 카테고리목록
create proc Cafe_order -- Cafe_order라는 프로시저를 생성
as
begin
select cate_name, isnull(cnt, 0) -- cate_name과 cnt 출력
from dbo.Category -- Category 테이블
left outer join
(
select count(*) as cnt , fk_cate from dbo.Cafe
group by fk_cate
) aa -- 카테고리 번호를 불러온 후 카테고리별 카페 총 개수 출력
on aa.fk_cate = dbo.Category.cate_id
order by cate_name asc; -- 카테고리 명을 ㄱㄴㄷ순으로 출력
end
go

--카페검색&카페목록&페이징
create proc List_Cafe(@ct_text nvarchar(20) ,@text nvarchar(50),@pagen int,@page int)
-- List_Cafe 프로시저를 만들어줌
-- @ct_text, @text, @pagen, @page변수 선언
as
begin
declare @table table --table 자료형으로 @table 변수 선언
(
 sn int, -- sn이라는 int형 컬럼 선언
 cnt int, --cnt라는 int형 컬럼 선언
 name nvarchar(50), --name이라는 nvarchar컬럼 선언
 make_date char(10), --make_date라느 char컬럼 선언
 cate_name nvarchar(20) --cate_name이라는 nvarchar컬럼 선언
)
-- @table변수에 아래 select된 내용을 넣어줌
insert into @table
--카페 목록 출력(카페 번호 , 카페에 가입된 회원수 , 카페명 , 커패생성일 , 카테고리명)
select sn,
(
-- 카페에 가입된 회원수 출력
select count(*) as num
from dbo.Member_join mj
where fk_cafe = sn
) as num,  name, convert(char(10), make_date, 120), cate_name
from dbo.Cafe ca
inner join dbo.Category cg
on ca.fk_cate = cg.cate_id

--검색시 카테고리가 null이거나 아무것도 선택하지 않을 시 모든 카페목록 출력
if(@ct_text is null or @ct_text = '')
begin
select top(@pagen)* from @table
where sn not in
(select top ((@page -1) * @pagen) sn from @table order by sn desc)
order by sn desc
end
else if(@ct_text = 'All')-- 카테고리랑 상관없이 카페명으로만 검색
begin
select top(@pagen) * from @table
where name like '%' + @text + '%' and sn not in
(select top ((@page -1) * @pagen) sn from @table order by sn desc)
order by sn desc
end
else--카테고리와 카페명으로 검색
begin
select top(@pagen) * from @table
where name like '%' + @text + '%' and cate_name = @ct_text and sn not in
(select top ((@page -1) * @pagen) sn from @table order by sn desc)
order by sn desc
end
end
go

--로그인
create proc loginChk(@mID varchar(20), @mName nvarchar(5))
as
begin
 declare @mChk smallint --로그인 결과를 담을 변수
 declare @mNameDB varchar(20) --해당아이디의 이름을 담을 변수
 --아이디에 맞는 이름을 검색하여 결과를 저장한다.
 set @mNameDB = (select name from member where id = @mID)

 --담겨져 있는 이름이 없을경우(회원가입이 되어 있지 않을경우)
 if(@mNameDB is null)
 begin
  set @mChk = 0
 end
 else
 begin
  --넘겨받은 이름과 DB검색결과에 담긴 이름이 같은경우(로그인처리) 
  if(@mNameDB = @mName)
  begin
   set @mChk = 1
  end
  else
  begin
   set @mChk = 2
  end
 end

 select @mChk as mChk
end