-----------------下面是一段測試的 -----------
Create Table test(tname varchar(10) ,gtime datetime)
insert into test
select 'A','2005-01-01 18:00' union all
select 'A','2005-01-01 19:25' union all
select 'A','2005-01-01 07:08' union all
select 'B','2005-01-01 18:00' union all
select 'b','2005-01-01 19:25' union all
select 'B','2005-01-01 07:08'
-----------------------------------------------------
---------------------下面這段是取不同用戶的第一次和最後一次--------
select tname,第一次=min(gtime),最後一次=max(gtime) from test group by tname,convert(varchar(10),gtime,120)
-----------------------結果如下--------------------
A 2005-01-01 07:08:00.000 2005-01-01 19:25:00.000
B 2005-01-01 07:08:00.000 2005-01-01 19:25:00.000
------------------刪除測試--------------
drop table test
--------或者用下面的語句也可以隻用一條就行
-----------------下面是一段測試的 -----------
Create Table test(tname varchar(10) ,gtime datetime)
insert into test
select 'A','2005-01-01 18:00' union all
select 'A','2005-01-01 19:25' union all
select 'A','2005-01-01 07:08' union all
select 'B','2005-01-01 18:00' union all
select 'b','2005-01-01 19:25' union all
select 'B','2005-01-01 07:08'
-----------------------------------------------------
---------------------下面這段是取不同用戶的第一次和最後一次--------
select tname,第一次=min(gtime),最後一次=max(gtime) from test group by tname ---隻要這條就行
-----------------------結果如下--------------------
A 2005-01-01 07:08:00.000 2005-01-01 19:25:00.000
B 2005-01-01 07:08:00.000 2005-01-01 19:25:00.000
------------------刪除測試--------------
drop table test
基本解决了,谢谢大家帮助
我的方法是:
select 用户,max(登录时间),min(登录时间)
from 表
where datediff(Day,convert(datetime,substring(convert(varchar,登录时间),1,10)),登录时间)<1
group by 用户,substring(convert(varchar,登录时间))
-----------------下面是一段測試的 -----------
Create Table test(tname varchar(10) ,gtime datetime)
insert into test
select 'A','2005-01-01 18:00' union all
select 'A','2005-01-01 19:25' union all
select 'A','2005-01-01 07:08' union all
select 'B','2005-01-01 18:00' union all
select 'b','2005-01-01 19:25' union all
select 'B','2005-01-01 07:08'
-----------------------------------------------------
---------------------下面這段是取不同用戶的第一次和最後一次--------
select * from
( select tname,gtime=min(gtime) from test group by tname
union
select tname,gtime=max(gtime) from test group by tname) a order by tname,gtime
-----------------------結果如下--------------------
A 2005-01-01 07:08:00.000
A 2005-01-01 19:25:00.000
B 2005-01-01 07:08:00.000
B 2005-01-01 19:25:00.000
------------------刪除測試--------------
drop table test
select * from
(select min(姓名) as name, min(登录时间) as 登录时间 from test group by 登录时间,姓名
union all
select max(姓名) as name, max(登录时间) as 登录时间 from test group by 登录时间,name) as a order by 姓名,日期