select * from tbl where id in ( select min(id) from tbl group by 日期)
表: 姓名 登录时间 A 2005-01-01 18:00 A 2005-01-01 19:25 A 2005-01-01 07:08
select max(姓名), min(登录时间) group by 登录时间 from 表
select max(姓名), max(登录时间) group by 登录时间 from 表
select top 1 * from tbl where 日期=getdate() order by 日期 asc
该死select max(姓名), max(登录时间) from 表 group by 登录时间
select top 1 * from tbl where 日期=getdate() order by 日期 desc 最后登陆时间
可以按用户和时间group吗,得到不同用户的第1次和最后1次
可以 select max(姓名), max(登录时间) from 表 group by 姓名,登录时间
可以 select top 1 * from tbl group by 用户,covert('114',日期) order by 日期 desc
create table #ttt ( 姓名 varchar(10), 登录时间 datetime )insert into #ttt 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' select * from #ttt a where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间<a.登录时间) union allselect * from #ttt a where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间>a.登录时间)order by 登录时间------------------------------- A 2005-01-01 07:08:00.000 A 2005-01-01 19:25:00.000 (所影响的行数为 2 行)
-排序方式改一点 select * from #ttt a where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间<a.登录时间) union allselect * from #ttt a where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间>a.登录时间)order by 姓名,登录时间
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 姓名,日期
-----------------下面是一段測試的 ----------- 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 用户,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 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
获得datetime的日期方式用转换字符,慢阿,大家有好的方法吗
select 用户,max(登录时间),min(登录时间) from 表 group by 用户,convert(varchar(10),登录时间,120)
Softlee81307(孔腎) : 还要区分日期的啊
試一下這條select 用户,max(登录时间),min(登录时间) from 表 group by 用户,convert(varchar(10),登录时间,120)
-----------------下面是一段測試的 ----------- 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
--取得每天的第一个最后一个记录 select max(姓名),max(登录时间) from 表 group by convert(char(10),登录时间120)
姓名 登录时间
A 2005-01-01 18:00
A 2005-01-01 19:25
A 2005-01-01 07:08
select max(姓名), max(登录时间) from 表 group by 姓名,登录时间
select top 1 * from tbl group by 用户,covert('114',日期) order by 日期 desc
(
姓名 varchar(10),
登录时间 datetime
)insert into #ttt
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'
select * from #ttt a
where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间<a.登录时间)
union allselect * from #ttt a
where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间>a.登录时间)order by 登录时间-------------------------------
A 2005-01-01 07:08:00.000
A 2005-01-01 19:25:00.000
(所影响的行数为 2 行)
select * from #ttt a
where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间<a.登录时间)
union allselect * from #ttt a
where not exists (select 1 from #ttt where 姓名=a.姓名 and 登录时间>a.登录时间)order by 姓名,登录时间
(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 姓名,日期
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 用户,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 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
from 表
group by 用户,convert(varchar(10),登录时间,120)
from 表
group by 用户,convert(varchar(10),登录时间,120)
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
select max(姓名),max(登录时间) from 表 group by convert(char(10),登录时间120)