M_L 会员登陆表
最近一个月中会员登陆记录中,中间有超过五天没有登陆会员Username(用户名) U_time 登陆时间
a 2008-1-1 10:30
a 2008-1-2 10:30
b 2008-1-1 11:30
c 2008-1-1 12:30
最近一个月中会员登陆记录中,中间有超过五天没有登陆会员Username(用户名) U_time 登陆时间
a 2008-1-1 10:30
a 2008-1-2 10:30
b 2008-1-1 11:30
c 2008-1-1 12:30
select count(*) from t
where 登录名 and 登录日期 is null
返回值大于5就是了
如果是后者的话
create table #M_L(Username char(1), U_time datetime)
insert into #M_L select 'a', '2008-1-1 10:30'
insert into #M_L select 'a', '2008-1-2 10:30'
insert into #M_L select 'b', '2008-1-1 11:30'
insert into #M_L select 'c', '2008-1-1 12:30'
select Username, count(1) Times_login_in_one_month from
(select Username, Year(U_time) U_year, month(U_time) U_month from #M_L) a
group by Username, U_year, U_month/*
Username Times_login_in_one_month
-------- ------------------------
a 2
b 1
c 1
*/
drop table #M_L
CREATE PROCEDURE [dbo].[pro_CURSOR]
AS
BEGIN
--声明一个游标
DECLARE MyCURSOR CURSOR FOR
SELECT login_date FROM table--打开游标
open MyCURSOR--声明两个变量
declare @ldate1 datetime
declare @ldate2 datetime--循环移动
fetch next from MyCURSOR into @ldate1
fetch next from MyCURSOR into @ldate2
while(@@fetch_status=0)
begin
if(@ldate2-@ldate1=5) return 1 fetch next from MyCURSOR into @ldate1
fetch next from MyCURSOR into @ldate2
endclose MyCURSOR
deallocate MyCURSOR
return 0
end
if(@ldate2-@ldate1>=5) return 1 --手误