比如我要根据今天的时间来查询用户或某个用户这7天来是否有连续登陆。
解决方案 »
- 高分求解 !向SQLSever2000 double 型字段插入数据时的精度
- 如何在SQL中找到指定的行并写入到一个新的表中..详情如下
- 有没有这样的触发器?当更新某一行的数据时,将有变化的字段以及新值和旧值,一齐插入到另外一个表中,相当于建立了一个字段更改纪录表
- 关于这个算法在SQL中应该怎么做?
- 把数据库里面所有的百度文字,都替换成可点击的超链接形式。救救俺
- 请教,我想将一个字段值在SQL语句中,自动补齐位数,是否可以做到,有这样的函数么?? 谢谢
- 这样写SQL对么?
- sql2005无法连接
- 关于sql 语句的优化!谢谢各位大侠!
- 如何控制触发器的互相触发???
- 数据库查询问题
- Windows 不能在 本地计算机 启动 SQL Server (SQLEXPRESS)
create table loginlog(logintime datetime,u_id int)insert into loginlog select '2011-12-16',907
insert into loginlog select '2011-12-17',907
insert into loginlog select '2011-12-18',907
insert into loginlog select '2011-12-14',1100
insert into loginlog select '2011-12-15',1100
insert into loginlog select '2011-12-16',1100
insert into loginlog select '2011-12-13',1200
insert into loginlog select '2011-12-14',1200
insert into loginlog select '2011-12-16',1200
insert into loginlog select '2011-12-17',1200
insert into loginlog select '2011-12-18',1200
go
那就实现连续3天的吧 比如说如果现在时间是2011-12-18 那么结果就显示用户907和1200
如果现在时间是2011-12-16 那么结果就显示用户1100
create table #loginlog(logintime datetime,u_id int)insert into #loginlog select '2011-12-16',907
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-14',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
declare @date datetime
SET @date='2011-12-16'
select a.u_id from #loginlog a
join (select u_id from #loginlog where logintime=DATEADD(D,-1,@date))b on b.u_id=a.u_id
join (select u_id from #loginlog where logintime=DATEADD(D,-2,@date))c on c.u_id=a.u_id
where logintime=@date
drop table #loginlog
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
insert into loginlog select '2011-12-17',907
insert into loginlog select '2011-12-18',907
insert into loginlog select '2011-12-14',1100
insert into loginlog select '2011-12-15',1100
insert into loginlog select '2011-12-16',1100
insert into loginlog select '2011-12-13',1200
insert into loginlog select '2011-12-14',1200
insert into loginlog select '2011-12-16',1200
insert into loginlog select '2011-12-17',1200
insert into loginlog select '2011-12-18',1200
go
/*
那就实现连续3天的吧 比如说如果现在时间是2011-12-18 那么结果就显示用户907和1200
如果现在时间是2011-12-16 那么结果就显示用户1100
*/select distinct u_id from loginlog
where logintime between convert(varchar(10),DATEADD(DD,-3,'2011-12-18'),120)
and CONVERT(varchar(10),'2011-12-18',120)
group by u_id
having COUNT(*)>=3/*
u_id
907
1200
*/
如果是根据今天,就直接把我指定的那个时间换成getdate()就好了
我这里是方便测试
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
where logintime between convert(varchar(10),DATEADD(DD,-3,'2011-12-18'),120)
and CONVERT(varchar(10),'2011-12-18',120)
group by u_id
having COUNT(*)>=3
create table #loginlog(logintime datetime,u_id int)insert into #loginlog select '2011-12-16',907
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
--同一个帐号,连续登陆的天数
;WITH cte AS
(
SELECT b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,logintime),112)AS dn,COUNT(1) AS counts
FROM
(
SELECT u_id,ROW_NUMBER()OVER(PARTITION BY u_id ORDER BY logintime) AS sn,logintime
FROM #loginlog
)AS b
GROUP BY b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,b.logintime),112)
)
SELECT * FROM cteu_id dn counts
----------- -------- -----------
1200 20111212 1
1100 20111213 3
1200 20111213 4
907 20111215 3(4 行受影响)
create table #loginlog(logintime datetime,u_id int)insert into #loginlog select '2011-12-16',907
insert into #loginlog select '2011-12-17',907
insert into #loginlog select '2011-12-18',907
insert into #loginlog select '2011-12-14',1100
insert into #loginlog select '2011-12-15',1100
insert into #loginlog select '2011-12-16',1100
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200
go
--同一个帐号,连续登陆的天数
;WITH cte AS
(
SELECT b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,logintime),112)AS dn,COUNT(1) AS counts
FROM
(
SELECT u_id,ROW_NUMBER()OVER(PARTITION BY u_id ORDER BY logintime) AS sn,logintime
FROM #loginlog
)AS b
GROUP BY b.u_id,CONVERT(CHAR(8),DATEADD(day,-sn,b.logintime),112)
)
SELECT * FROM cte where counts>7--鸭子,你又结贴了u_id dn counts
----------- -------- -----------
1200 20111212 1
1100 20111213 3
1200 20111213 4
907 20111215 3(4 行受影响)
你的还是有点问题呀,DATEADD(DD,-3,'2011-12-18')这里的DD如果大于-5结果就会显示连续登陆次数为5啊,虽然是5天,但是13和15不连续啊,应该还是显示次数为4的吧
insert into #loginlog select '2011-12-13',1200
insert into #loginlog select '2011-12-15',1200
insert into #loginlog select '2011-12-16',1200
insert into #loginlog select '2011-12-17',1200
insert into #loginlog select '2011-12-18',1200