/* name logindate a1 2011-1-2 a1 2011-1-3 a1 2011-1-4 a1 2011-1-7 a1 2011-1-12 a1 2011-1-13 a1 2011-1-16 a2 2011-1-7 a2 2011-1-8 a2 2011-1-10 a2 2011-1-11 a2 2011-1-13 a2 2011-1-24 --------------------------------------------- 我需要的结果是: name start_day end_day logindays a1 2011-1-2 2011-1-4 3 a2 2011-1-7 2011-1-8 2 a2 2011-1-10 2011-1-11 2 */ --> 测试数据:[tbl] if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([name] varchar(2),[logindate] date) insert [tbl] select 'a1','2011-1-2' union all select 'a1','2011-1-3' union all select 'a1','2011-1-4' union all select 'a1','2011-1-7' union all select 'a1','2011-1-12' union all select 'a1','2011-1-13' union all select 'a1','2011-1-16' union all select 'a2','2011-1-7' union all select 'a2','2011-1-8' union all select 'a2','2011-1-10' union all select 'a2','2011-1-11' union all select 'a2','2011-1-13' union all select 'a2','2011-1-24' --方法1 ;with t as( select [name],[logindate], (select min(b.[logindate]) from tbl b where b.[logindate]>=a.[logindate] and b.name=a.name and not exists (select * from tbl c where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp from tbl a ),m as( select [name],min([logindate]) as start_day,max(grp) as end_day from t group by grp,name ) select *,(datediff(dd,start_day,end_day)+1) as logindays from m a where (datediff(dd,start_day,end_day)+1) in( select max(datediff(dd,start_day,end_day)+1) from m b where a.name=b.name) --------------------------------------------------------------------------- --------------------------------------------------------------------------- --方法2 declare @date datetime select @date = min(logindate) from tbl ;with ach as ( select [name],logindate, id=row_number() over (partition by [name] order by logindate) from tbl ), t as( select [name],min(logindate) mindate,max(logindate) maxdate, (datediff(dd,min(logindate),max(logindate))+1) dddate from ach group by [name],datediff(dd,@date,logindate)-id --order by [name],mindate ) select * from t a where dddate in(select max(dddate) from t b where a.name=b.name) -------------------------------------------------------------------------- -------------------------------------------------------------------------- --方法3 ;with t as ( select name,[logindate],dateadd(dd, -row_number()over(partition by name order by [logindate]),[logindate]) as diff from tbl ), m as( select name,min([logindate]) as start_day,max([logindate]) as end_day, (datediff(dd,min([logindate]),max([logindate]))+1) as logindays from t group by name,diff ) select * from m a where logindays in(select MAX(logindays) from m b where a.name=b.name) /* name start_day end_day logindays a1 2011-01-02 2011-01-04 3 a2 2011-01-10 2011-01-11 2 a2 2011-01-07 2011-01-08 2 */ 有兴趣可以看看我的博客 http://blog.csdn.net/travylee/article/details/7392436
if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([name] varchar(2),[logindate] datetime) insert [tbl] select 'a1','2011-1-2' union all select 'a1','2011-1-3' union all select 'a1','2011-1-4' union all select 'a1','2011-1-5' union all select 'a1','2011-1-6' union all select 'a1','2011-1-7' union all select 'a1','2011-1-9' union all select 'a2','2011-1-7' union all select 'a2','2011-1-8' union all select 'a2','2011-1-9' union all select 'a2','2011-1-10' union all select 'a2','2011-1-11' union all select 'a2','2011-1-15'SELECT DISTINCT m.name FROM ( SELECT name, logindate = DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY name ORDER BY logindate), logindate) FROM (SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) t ) M GROUP BY m.name, M.logindate HAVING COUNT(*) >= 6/* name a1 */
if OBJECT_ID('tb') is not null drop table tb go create table tb ( username varchar(10), logindate datetime, ) go insert tb select 'A','2013-08-01' union all select 'B','2013-08-01' union all select 'B','2013-08-02' union all select 'A','2013-08-02' union all select 'C','2013-08-02' union all select 'A','2013-08-03' union all select 'C','2013-08-03' union all select 'B','2013-08-03' union all select 'D','2013-08-03' union all select 'A','2013-08-04' union all select 'B','2013-08-04' union all select 'C','2013-08-04' union all select 'C','2013-08-05' union all select 'D','2013-08-06' union all select 'D','2013-08-07' union all select 'B','2013-08-08' union all select 'B','2013-08-9' union all select 'C','2013-08-9' union all select 'D','2013-08-9' union all select 'A','2013-08-10' GO--SQL Server2000的处理起来就稍微麻烦点了if OBJECT_ID('up_test') is not null drop proc up_test go create proc up_test ( @days int ) as --如果原表没有自增的主键,那么先加上主键 select ID=IDENTITY(int,1,1),* into #t from tb --根据username分组生成按照时间递增的序号 select rcnt=(select COUNT(1) from #t b where a.username=b.username and a.ID>=b.ID) ,* into #res from #t a-- order by username,1--找出连续的时间段和天数select username, st, ed, DATEDIFF(DD,st,ed)+1 as listdays from ( select username, dt, MIN(logindate) as st, MAX(logindate) as ed from( select username, logindate, DATEADD(DD,-rcnt,logindate) as dt from #res )t group by username,dt ) a where DATEDIFF(DD,st,ed)+1=@days goexec up_test 4 /* username st ed listdays ----- ------- ----------------- ----------- ----------- A 2013-08-01 00:00:00.000 2013-08-04 00:00:00.000 4 B 2013-08-01 00:00:00.000 2013-08-04 00:00:00.000 4 C 2013-08-02 00:00:00.000 2013-08-05 00:00:00.000 4 */
if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([name] varchar(2),[logindate] datetime) insert [tbl] select 'a1','2011-1-2' union all select 'a1','2011-1-3' union all select 'a1','2011-1-4' union all select 'a1','2011-1-5' union all select 'a1','2011-1-6' union all select 'a1','2011-1-7' union all select 'a1','2011-1-9' union all select 'a2','2011-1-7' union all select 'a2','2011-1-8' union all select 'a2','2011-1-9' union all select 'a2','2011-1-10' union all select 'a2','2011-1-11' union all select 'a2','2011-1-15'--2000的话,用子查询 SELECT DISTINCT t.name FROM ( SELECT name,logindate,rowid=(SELECT COUNT(DISTINCT logindate) FROM tbl n WHERE n.NAME = m.NAME AND CONVERT(CHAR(10), n.logindate, 120) <= m.logindate) FROM(SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) m ) t GROUP BY t.name, (DATEADD(DAY, -rowid, t.logindate)) HAVING COUNT(*) >= 6/* name a1 */
--2000的话,用子查询 SELECT DISTINCT t.name FROM ( SELECT name,logindate,rowid=(SELECT COUNT(DISTINCT logindate) FROM tbl n WHERE n.NAME = m.NAME AND CONVERT(CHAR(10), n.logindate, 120) <= m.logindate) FROM(SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) m ) t GROUP BY t.name, (DATEADD(DAY, -rowid, t.logindate)) HAVING COUNT(*) >= 6
DECLARE @begindate DATETIME, @enddate DATETIME
SELECT @begindate = '2013-01-01', @enddate='2013-02-01'
SELECT userid ,
MIN([date]) , --第一次登陆时间
MAX([date]) , --最后一次登陆时间
cnt = COUNT(1) --登陆次数
FROM tablename
WHERE [date] BETWEEN @begindate AND @enddate
GROUP BY userid
解决这类问题,我们有一个最基本的思路:
step 1:找出间断之后的点,为他们分配行号(这是孤岛的起点)
step 2:找出间断之前的点,为他们分配行号(这是孤岛的终点)
step 3:以行号相等作为条件,匹配孤岛的起点和终点
在实现以上三步后,我们基本上就能解决这类问题了,一下我以三种方法演示:
/*
name logindate
a1 2011-1-2
a1 2011-1-3
a1 2011-1-4
a1 2011-1-7
a1 2011-1-12
a1 2011-1-13
a1 2011-1-16
a2 2011-1-7
a2 2011-1-8
a2 2011-1-10
a2 2011-1-11
a2 2011-1-13
a2 2011-1-24
---------------------------------------------
我需要的结果是:
name start_day end_day logindays
a1 2011-1-2 2011-1-4 3
a2 2011-1-7 2011-1-8 2
a2 2011-1-10 2011-1-11 2
*/
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([name] varchar(2),[logindate] date)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-12' union all
select 'a1','2011-1-13' union all
select 'a1','2011-1-16' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-13' union all
select 'a2','2011-1-24'
--方法1
;with t as(
select [name],[logindate],
(select min(b.[logindate]) from tbl b
where b.[logindate]>=a.[logindate] and b.name=a.name
and not exists (select * from tbl c
where c.[logindate]=dateadd(dd,1,b.[logindate]) and c.name=b.name)) as grp
from tbl a
),m
as(
select [name],min([logindate]) as start_day,max(grp) as end_day
from t group by grp,name
)
select *,(datediff(dd,start_day,end_day)+1) as logindays from m a
where (datediff(dd,start_day,end_day)+1) in(
select max(datediff(dd,start_day,end_day)+1) from m b
where a.name=b.name)
---------------------------------------------------------------------------
---------------------------------------------------------------------------
--方法2
declare @date datetime
select @date = min(logindate) from tbl
;with ach as
(
select [name],logindate,
id=row_number() over (partition by [name] order by logindate)
from tbl
),
t as(
select [name],min(logindate) mindate,max(logindate) maxdate,
(datediff(dd,min(logindate),max(logindate))+1) dddate
from ach
group by [name],datediff(dd,@date,logindate)-id
--order by [name],mindate
)
select * from t a where dddate in(select max(dddate) from t b where
a.name=b.name)
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--方法3
;with t as
(
select name,[logindate],dateadd(dd,
-row_number()over(partition by name
order by [logindate]),[logindate]) as diff from tbl
),
m as(
select name,min([logindate]) as start_day,max([logindate]) as end_day,
(datediff(dd,min([logindate]),max([logindate]))+1) as logindays
from t
group by name,diff
)
select * from m a
where logindays in(select MAX(logindays) from m b
where a.name=b.name)
/*
name start_day end_day logindays
a1 2011-01-02 2011-01-04 3
a2 2011-01-10 2011-01-11 2
a2 2011-01-07 2011-01-08 2
*/
有兴趣可以看看我的博客
http://blog.csdn.net/travylee/article/details/7392436
create table [tbl]([name] varchar(2),[logindate] datetime)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-5' union all
select 'a1','2011-1-6' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-9' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-9' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-15'SELECT DISTINCT m.name
FROM
(
SELECT
name,
logindate = DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY name ORDER BY logindate), logindate)
FROM (SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) t
) M
GROUP BY m.name, M.logindate
HAVING COUNT(*) >= 6/*
name
a1
*/
服务器: 消息 195,级别 15,状态 10,行 6
'ROW_NUMBER' 不是可以识别的 函数名。
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: 't' 附近有语法错误。
drop table tb
go
create table tb
(
username varchar(10),
logindate datetime,
)
go
insert tb
select 'A','2013-08-01' union all
select 'B','2013-08-01' union all
select 'B','2013-08-02' union all
select 'A','2013-08-02' union all
select 'C','2013-08-02' union all
select 'A','2013-08-03' union all
select 'C','2013-08-03' union all
select 'B','2013-08-03' union all
select 'D','2013-08-03' union all
select 'A','2013-08-04' union all
select 'B','2013-08-04' union all
select 'C','2013-08-04' union all
select 'C','2013-08-05' union all
select 'D','2013-08-06' union all
select 'D','2013-08-07' union all
select 'B','2013-08-08' union all
select 'B','2013-08-9' union all
select 'C','2013-08-9' union all
select 'D','2013-08-9' union all
select 'A','2013-08-10'
GO--SQL Server2000的处理起来就稍微麻烦点了if OBJECT_ID('up_test') is not null
drop proc up_test
go
create proc up_test
(
@days int
)
as
--如果原表没有自增的主键,那么先加上主键
select
ID=IDENTITY(int,1,1),* into #t from tb
--根据username分组生成按照时间递增的序号
select
rcnt=(select COUNT(1) from #t b where a.username=b.username and a.ID>=b.ID) ,* into #res from #t a-- order by username,1--找出连续的时间段和天数select
username,
st,
ed,
DATEDIFF(DD,st,ed)+1 as listdays
from
(
select
username,
dt,
MIN(logindate) as st,
MAX(logindate) as ed
from(
select
username,
logindate,
DATEADD(DD,-rcnt,logindate) as dt
from #res
)t
group by
username,dt
) a
where DATEDIFF(DD,st,ed)+1=@days
goexec up_test 4
/*
username st ed listdays
----- ------- ----------------- ----------- -----------
A 2013-08-01 00:00:00.000 2013-08-04 00:00:00.000 4
B 2013-08-01 00:00:00.000 2013-08-04 00:00:00.000 4
C 2013-08-02 00:00:00.000 2013-08-05 00:00:00.000 4
*/
create table [tbl]([name] varchar(2),[logindate] datetime)
insert [tbl]
select 'a1','2011-1-2' union all
select 'a1','2011-1-3' union all
select 'a1','2011-1-4' union all
select 'a1','2011-1-5' union all
select 'a1','2011-1-6' union all
select 'a1','2011-1-7' union all
select 'a1','2011-1-9' union all
select 'a2','2011-1-7' union all
select 'a2','2011-1-8' union all
select 'a2','2011-1-9' union all
select 'a2','2011-1-10' union all
select 'a2','2011-1-11' union all
select 'a2','2011-1-15'--2000的话,用子查询
SELECT DISTINCT t.name
FROM
(
SELECT name,logindate,rowid=(SELECT COUNT(DISTINCT logindate) FROM tbl n WHERE n.NAME = m.NAME AND CONVERT(CHAR(10), n.logindate, 120) <= m.logindate)
FROM(SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) m
) t
GROUP BY t.name, (DATEADD(DAY, -rowid, t.logindate))
HAVING COUNT(*) >= 6/*
name
a1
*/
CREATE TABLE [tbl] (
[name] [varchar] (50) NULL,
[logindate] [datetime] NULL)
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:57:19')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-10 13:58:57')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:58:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:16')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:46:20')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 12:00:44')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-29 17:54:15')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:13:18')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:08:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 8:58:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 13:10:19')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 14:54:57')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:05:36')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 18:34:55')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-6 19:03:28')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:12:46')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:51:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:53:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 19:17:54')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:29:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:06:04')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:07:22')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:44:05')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 17:31:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:16:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-29 17:59:53')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-3 10:41:18')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:16:53')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:10:34')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:52:07')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 14:42:45')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:44')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 19:16:13')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 10:25:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:51:43')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:07:48')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:43:15')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:41:02')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:13:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:45:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-8 11:25:46')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:04:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:18:36')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:02:33')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-27 16:01:11')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:16:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:31:00')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 14:48:28')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:54:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:41:29')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:56:54')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:57:50')用楼上的语句查询就会有问题
--2000的话,用子查询
SELECT DISTINCT t.name
FROM
(
SELECT name,logindate,rowid=(SELECT COUNT(DISTINCT logindate) FROM tbl n WHERE n.NAME = m.NAME AND CONVERT(CHAR(10), n.logindate, 120) <= m.logindate)
FROM(SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) m
) t
GROUP BY t.name, (DATEADD(DAY, -rowid, t.logindate))
HAVING COUNT(*) >= 6/*
name
a1
*/
CREATE TABLE [tbl] (
[name] [varchar] (50) NULL,
[logindate] [datetime] NULL)
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:57:19')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-10 13:58:57')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:58:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:16')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:46:20')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 12:00:44')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-29 17:54:15')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:13:18')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:08:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 8:58:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 13:10:19')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 14:54:57')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:05:36')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 18:34:55')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-6 19:03:28')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:12:46')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:51:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:53:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 19:17:54')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:29:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:06:04')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:07:22')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:44:05')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 17:31:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:16:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-29 17:59:53')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-3 10:41:18')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:16:53')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:10:34')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:52:07')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 14:42:45')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:42:44')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 19:16:13')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-30 10:25:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:51:43')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:07:48')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:43:15')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:41:02')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:13:32')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-31 13:45:50')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-8 11:25:46')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:04:49')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-17 15:18:36')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-23 9:02:33')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-27 16:01:11')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:16:37')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:31:00')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 14:48:28')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-1 11:54:23')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-7-8 16:41:29')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-25 15:56:54')
INSERT [tbl] ([name],[logindate]) VALUES ( N'147852',N'2013-8-28 11:57:50')
--2000的话,用子查询
SELECT DISTINCT t.name
FROM
(
SELECT name,logindate,rowid=(SELECT COUNT(DISTINCT logindate) FROM tbl n WHERE n.NAME = m.NAME AND CONVERT(CHAR(10), n.logindate, 120) <= m.logindate)
FROM(SELECT DISTINCT name, logindate=CONVERT(CHAR(10), logindate, 120) FROM tbl) m
) t
GROUP BY t.name, (DATEADD(DAY, -rowid, t.logindate))
HAVING COUNT(*) >= 6