--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,loginTime datetime,exitTime datetime) insert into #T select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08'select ID,convert(varchar(10),loginTime,120)loginDate,cn=count(1) from #T group by ID,convert(varchar(10),loginTime,120) having count(1)>1/* ID loginDate cn ----------- ---------- ----------- 1 2008-05-14 2 3 2008-05-14 2 */
select id , count(*) 次数 from ( select id , convert(varchar(10),loginTime,120) from t1 group by id , convert(varchar(10),loginTime,120) having count(*) >= 2 ) t group by id
select id,count(1) from ta group by id,convert(char(10),logintime,120) having count(1) > 1
select id,count(1) as '次数' from t1 group by id,convert(varchar(10),logintime,120) having count(1)>1
/* -- Author:Flystone -- Version:V1.001 Date:2008-05-15 初稿 -- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常 -- 2、 增加了形如yyyy-mm-dd hh:mm:ss -- yyyy-m-d h:m:s 格式的处理 */-- Test Data: ta If object_id('ta') is not null Drop table ta Go Create table ta(ID int,loginTime datetime,exitTime smalldatetime) Go Insert into ta select 1,'2008-5-14 20:33:08','2008-5-14' union all select 1,'2008-5-14 23:33:08','2008-5-14' union all select 2,'2008-5-14 10:33:08','2008-5-14' union all select 3,'2008-5-14 10:33:08','2008-5-14' union all select 3,'2008-5-14 12:33:08','2008-5-14' union all select 4,'2008-5-15 10:33:08','2008-5-15' union all select 4,'2008-5-16 10:33:08','2008-5-16' Go --Startselect id,count(1) from ta group by id,convert(char(10),logintime,120) having count(1) > 1--Result: /*id ----------- ----------- 1 2 3 2(所影响的行数为 2 行)*/ --End
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,loginTime datetime,exitTime datetime) insert into #T select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08' go select ID,count(*) cnt from #T group by ID,convert(varchar(10),logintime,120) having count(convert(varchar(10),logintime,120))>=2 go drop table #T/* ID cnt ----------- ----------- 1 2 3 2(2 row(s) affected) */
借楼楼数据if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,loginTime datetime,exitTime datetime) insert into #T select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08'select id,count(1) as '次数' from #T group by id,convert(varchar(10),logintime,120) having count(1)>1id 次数 1 2 3 2
create table #a(id int ,l datetime ,x datetime)insert into #a(id,l,x) select 1 , '2008-5-14 20:33:08', '2008-5-14 22:33:08' union all select 1 , '2008-5-14 23:33:08', '008-5-14 23:35:08' union all select 2 , '2008-5-14 10:33:08', '2008-5-14 11:33:08' union all select 3 , '2008-5-14 10:33:08', '2008-5-14 11:33:08' union all select 3 , '2008-5-14 12:33:08', '2008-5-14 12:39:08' union all select 4 , '2008-5-15 10:33:08', '2008-5-15 10:50:08' union all select 4 ,'2008-5-16 10:33:08', '2008-5-16 10:38:08' select id,count(id) from #a group by id ,CONVERT(char(10),l, 121) having count(id)>1 /* 1 2 3 2 */ drop table #a
select id,count(id),datepart(d,logintime) from your_table where group by id,datediff(d,loginTime,getdate())
select id,count(id),datepart(d,logintime) from your_table group by id,datediff(d,loginTime,getdate())
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,loginTime datetime,exitTime datetime)
insert into #T
select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all
select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all
select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all
select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all
select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08'select ID,convert(varchar(10),loginTime,120)loginDate,cn=count(1) from #T group by ID,convert(varchar(10),loginTime,120) having count(1)>1/*
ID loginDate cn
----------- ---------- -----------
1 2008-05-14 2
3 2008-05-14 2
*/
(
select id , convert(varchar(10),loginTime,120) from t1 group by id , convert(varchar(10),loginTime,120) having count(*) >= 2
) t
group by id
from ta
group by id,convert(char(10),logintime,120)
having count(1) > 1
from t1
group by id,convert(varchar(10),logintime,120)
having count(1)>1
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,loginTime datetime,exitTime smalldatetime)
Go
Insert into ta
select 1,'2008-5-14 20:33:08','2008-5-14' union all
select 1,'2008-5-14 23:33:08','2008-5-14' union all
select 2,'2008-5-14 10:33:08','2008-5-14' union all
select 3,'2008-5-14 10:33:08','2008-5-14' union all
select 3,'2008-5-14 12:33:08','2008-5-14' union all
select 4,'2008-5-15 10:33:08','2008-5-15' union all
select 4,'2008-5-16 10:33:08','2008-5-16'
Go
--Startselect id,count(1)
from ta
group by id,convert(char(10),logintime,120)
having count(1) > 1--Result:
/*id
----------- -----------
1 2
3 2(所影响的行数为 2 行)*/
--End
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,loginTime datetime,exitTime datetime)
insert into #T
select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all
select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all
select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all
select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all
select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08'
go
select ID,count(*) cnt
from #T
group by ID,convert(varchar(10),logintime,120)
having count(convert(varchar(10),logintime,120))>=2
go
drop table #T/*
ID cnt
----------- -----------
1 2
3 2(2 row(s) affected)
*/
create table #T (ID int,loginTime datetime,exitTime datetime)
insert into #T
select 1,'2008-5-14 20:33:08','2008-5-14 22:33:08' union all
select 1,'2008-5-14 23:33:08','2008-5-14 23:35:08' union all
select 2,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 10:33:08','2008-5-14 11:33:08' union all
select 3,'2008-5-14 12:33:08','2008-5-14 12:39:08' union all
select 4,'2008-5-15 10:33:08','2008-5-15 10:50:08' union all
select 4,'2008-5-16 10:33:08','2008-5-16 10:38:08'select id,count(1) as '次数'
from #T group by id,convert(varchar(10),logintime,120) having count(1)>1id 次数
1 2
3 2
select
1 , '2008-5-14 20:33:08', '2008-5-14 22:33:08'
union all select
1 , '2008-5-14 23:33:08', '008-5-14 23:35:08'
union all select
2 , '2008-5-14 10:33:08', '2008-5-14 11:33:08'
union all select
3 , '2008-5-14 10:33:08', '2008-5-14 11:33:08'
union all select
3 , '2008-5-14 12:33:08', '2008-5-14 12:39:08'
union all select
4 , '2008-5-15 10:33:08', '2008-5-15 10:50:08'
union all select
4 ,'2008-5-16 10:33:08', '2008-5-16 10:38:08'
select id,count(id) from #a
group by id ,CONVERT(char(10),l, 121)
having count(id)>1
/*
1 2
3 2
*/
drop table #a
convert(varchar(10),logintime,120)将精确的时间转换成天,小时,分钟,秒,截取