数据库如何获取一段时间内的数据（按周/月）

[chenlanglin]

工作上遇到个麻烦（SQL Server）：

我有个日志表，Log_PlayTime[logdate,number,nickname,logintime,logouttime,playtime,total]
分别表示 日志时间，编号，昵称，登入时间，登出时间，在线时间，总在线时间。

要求 写存储过程
在页面上可以选择按周查看，还是按月查看。（从第几周到第几周或者 从第几个月到第几个月）

如果按周查看，则查找在时间段内，每周登录过1次的玩家的数量
如果按月查询，则查找在时间段内，每月登录过4次的玩家数量
(注：玩家每上一次线都会有记录，如果玩家在某天登录了三次，只能算一次)

显示结果：
如果是周查看，显示每周人数
如果是月查看，显示每月的人数

[db385208309]

这个应该不难，，就判断多点。。。。。没时间写，，，

[nightmaple]

create proc GetUserCount(
	@Type int,		--为1按周统计，为其它按月统计
	@SDate int,
	@EDate int
)
as
if @Type=1
begin
	select datename(week,logdate) as weeks,COUNT(*) as usercount 
	from (select distinct logdate,number from Log_PlayTime) 
	where datename(week,logdate) between @SDate and @EDate 
	group by datename(week,logdate)
end
else 
begin
	select datename(month,logdate) as months,COUNT(*) as usercount 
	from (select distinct logdate,number from Log_PlayTime) 
	where datename(month,logdate) between @SDate and @EDate 
	group by datename(month,logdate)
	having COUNT(*)>3
end
return
go

[thinclient]

[Quote=引用楼主 chenlanglin 的回复:]
工作上遇到个麻烦（SQL Server）：

我有个日志表，Log_PlayTime[logdate,number,nickname,logintime,logouttime,playtime,total]
分别表示 日志时间，编号，昵称，登入时间，登出时间，在线时间，总在线时间。

要求 写存储过程
在页面上可以选择按周查看，还是按月查看。（从第几周到第几周或者 从第几个月到第几个月……
[/Quote]
兄弟是这样,你要是帖出测试数据, 答案就来的快多了, 大手门不喜欢干别人都会干的事

[chenlanglin]

nightmaple
我要查询的是登录过四次的用户数，不是次数
COUNT(*) as usercount
这样出来的是次数吧
?

[chenlanglin]

Create Proc [dbo].[houtai_subarea_activityPerson]

@type int,--0:周,1:月
@bdate datetime ,
@edate datetime
AS
declare @beginWeek int
declare @endWeek int
declare @beginDate datetime
declare @endDate datetime
declare @temp int
BEGIN

create table #tableDaytime(keyid int,tDaytime int) --dateiem table
create table #tableActivityNum (tDaytime int,activityNum int) --the data table

if(@type=0) --周
begin
set @beginWeek=convert(int, datename(week,@bdate)) --获取开始日期的星期数
set @EndWeek=convert(int, datename(week,@edate-1))--获取结束日期的星期数

if(@EndWeek=convert(int,datename(week,convert(varchar(10), year(@edate))+'-12-31')))
begin
set @EndWeek=@EndWeek+1
end
else
begin
set @EndWeek=convert(int,datename(week,@edate))
end
while @beginWeek< @EndWeek
begin
--插入日期到临时表
Insert Into #tableDaytime(keyid,tDayTime)
values(0,@beginWeek)
--插入日期和活跃用户数到临时表
Insert into #tableActivityNum
Select @beginWeek,count(distinct(number)) from Log_Playtime
Where number in(
Select number from Log_Playtime Where datename(week,logdate)>= @beginWeek
and datename(week,logdate)<@beginWeek+1
group by number
having count(number)>=1)--筛选有过次登录的
set @beginWeek = @beginWeek + 1
end
end
else if(@type=1) --月
begin
set @temp=month(@bdate)
while @temp<=month(@edate)
begin
--插入日期到临时表
Insert Into #tableDaytime(keyid,tDaytime)
values(0,@temp)
--插入日期和活跃用户数到临时表
Insert Into #tableActivityNum
Select @temp,count(distinct(number)) from Log_Playtime
Where number in (
Select number from Log_PlayTime Where month(logdate)>= @temp
and month(logdate)<@temp+1
group by number
having count(number)>3) --筛选有过次登录的
set @temp=@temp+1
end
end

Select dt.tDaytime as tDayTime,oln.activityNum as num
From #tableDaytime as dt left join #tableActivityNum as oln
on oln.tDayTime=dt.tDayTime
group by dt.tDaytime,oln.activityNum

drop table #tableDaytime
drop table #tableActivityNum

END

不知道这中写法对不对，各位高手有空帮我看下

[chenlanglin]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go











Create Proc [dbo].[houtai_subarea_activityPerson]

@type int,--0:周,1:月
@bdate datetime ,
@edate datetime
AS
declare @beginWeek int
declare @endWeek int
declare @beginDate datetime
declare @endDate datetime
declare @temp int
BEGIN

create table #tableDaytime(keyid int,tDaytime int) --dateiem table
create table #tableActivityNum (tDaytime int,activityNum int) --the data table

--set @bdate=convert(varchar(10),@bdate,120)+' 00:00:00.000'
--set @edate=convert(varchar(10),@edate,120)+' 23:59:59.000'

------在临时表中插入第一列,即时间
if(@type=0) --周
begin
set @beginWeek=convert(int, datename(week,@bdate)) --获取开始日期的星期数
set @EndWeek=convert(int, datename(week,@edate-1))--获取结束日期的星期数

if(@EndWeek=convert(int,datename(week,convert(varchar(10), year(@edate))+'-12-31')))
begin
set @EndWeek=@EndWeek+1
end
else
begin
set @EndWeek=convert(int,datename(week,@edate))
end
while @beginWeek< @EndWeek
begin
--插入日期到临时表
Insert Into #tableDaytime(keyid,tDayTime)
values(0,@beginWeek)
--插入日期和活跃用户数到临时表
Insert into #tableActivityNum
Select @beginWeek,count(distinct(number)) from Log_Playtime
Where number in(
Select number from Log_Playtime Where datename(week,logdate)>= @beginWeek
and datename(week,logdate)<@beginWeek+1
group by number
having count(number)>=1)--筛选有过次登录的
set @beginWeek = @beginWeek + 1
end
end
else if(@type=1) --月
begin
set @temp=month(@bdate)
while @temp<=month(@edate)
begin
--插入日期到临时表
Insert Into #tableDaytime(keyid,tDaytime)
values(0,@temp)
--插入日期和活跃用户数到临时表
Insert Into #tableActivityNum
Select @temp,count(distinct(number)) from Log_Playtime
Where number in (
Select number from Log_PlayTime Where month(logdate)>= @temp
and month(logdate)<@temp+1
group by number
having count(number)>3) --筛选有过次登录的
set @temp=@temp+1
end
end

Select dt.tDaytime as tDayTime,oln.activityNum as num
From #tableDaytime as dt left join #tableActivityNum as oln
on oln.tDayTime=dt.tDayTime
group by dt.tDaytime,oln.activityNum
order by dt.tDaytime

drop table #tableDaytime
drop table #tableActivityNum

END