[最优解释] select a.* from tablename as a inner join (select 用户名,MIN(登录时间) as 登录时间 from tablename group by 用户名) as b on a.用户名=b.用户名 and a.登录时间=b.登录时间 [其他解释]
--方法一05新增: select id,code,listdate from (select rid=row_number()over (partition by convert(varchar(10),listdate) order by listdate desc),* from #t1)as t where rid<=1
--方法二:使用cross apply select distinct b.* from #t1 as a cross apply (select top(1) * from #t1 where convert(varchar(10),a.listdate)=convert(varchar(10),listdate) order by listdate desc) as b
[其他解释] declare @tt table(name varchar(10),[time] varchar(20)) insert @tt select '用户a','1点' union all select '用户a','10点' union all select '用户b','3点' union all select '用户b','7点' union all select '用户c','0点' union all select '用户c','3点' select * from @tt as t where exists(select * from @tt where t.[time]<[time] and t.name = name)
select t.* from @tt as t cross apply(select * from @tt where t.[time]<[time] and t.name = name ) as p
select p.* from @tt as t cross apply(select * from @tt where t.[time]>[time] and t.name = name ) as p [其他解释] 就是想求出所有用户当天首次登录的记录。 [其他解释] --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([用户名] varchar(5),[登录时间] varchar(4)) insert [TB] select '用户a','1点' union all select '用户a','10点' union all select '用户b','3点' union all select '用户b','7点' union all select '用户c','0点' union all select '用户c','3点'
select * from [TB] t WHERE NOT EXISTS( SELECT 1 FROM Tb WHERE t.[用户名]=[用户名] and t.[登录时间]<[登录时间] )
DROP TABLE TB
[其他解释] 将desc改成asc
--方法一05新增: select id,code,listdate from (select rid=row_number()over (partition by convert(varchar(10),listdate) order by listdate asc),* from #t1)as t
where rid<=2
--方法二:使用cross apply select distinct b.* from #t1 as a cross apply (select top(2) * from #t1 where convert(varchar(10),a.listdate)=convert(varchar(10),listdate) order by listdate asc) as b
就是第一次出现的时间了 [其他解释]
SELECT 用户名,min(left(登录时间,len(登录时间)-1))+'点' as 登录时间 from tb GROUP BY 用户名
[其他解释] if object_id('logintime') is not null drop table logintime
insert into logintime select '用户a','1点 ' insert into logintime select '用户a','10点 ' insert into logintime select '用户b','3点 ' insert into logintime select '用户b','7点 ' insert into logintime select '用户c','0点 ' insert into logintime select '用户c','3点 '
select username,min(substring(ltime,1,charindex('点',ltime)-1))+'点' from logintime group by username
[其他解释]
if OBJECT_ID('test') is not null drop table test go create table test(name varchar(20),login_time varchar(10)) insert into test select '用户a', '1点' union all select '用户a', '10点' union all select '用户b', '3点'union all select '用户b', '7点' union all select '用户c', '0点'union all select '用户c', '3点' select name,MIN(newtime)+'点' from( select name,LEFT(login_time,charindex('点',login_time)-1) as newtime from test ) as b group by name
[其他解释] select username,min(logintime,charindex('点',logintime)-1)+'点' from table group by username [其他解释]
借用你的结构
SELECT A.用户名,A.[登录时间] FROM ( SELECT t.用户名,t.[登录时间], ROW_NUMBER() OVER (Partition by t.用户名 ORDER BY len(t.[登录时间]),t.[登录时间])as Tid FROM TB t)AS A WHERE A.Tid=1