一个sql语句你会写吗

[zw365579102]

userid username loginnanme
1 李斯 2009-10-12 16：40 11
2 王五 2009-10-12 16：41 12
3 成神 2009-10-12 16：42 13
1 李斯 2009-10-12 16：42 14
1 李斯 2009-11-12 16：42 15
1 李斯 2009-11-12 16：41 15
我要查询每个用户每天第一次登陆的信息，上表我想要的信息是
1 李斯 2009-10-12 16：40 11
1 李斯 2009-11-12 16：41 15
2 王五 2009-10-12 16：41 12
3 成神 2009-10-12 16：42 13
该怎么写？多谢赐教

[sgtzzc]

select *
from tb t
where 
  not exists(select 1 from tb where userid=t.userid and datediff(dd,loginnanme,t.loginnanme)=0 and loginnanme<t.loginnanme)

[快乐_石头]

select * from tb t 
where not exists(select 1 from tb where userid=t.userid and 時間<T.時間)

[好汉坡]

--> 测试数据: @tb
declare @tb table (userid int,username varchar(4),loginname datetime,c4 int)
insert into @tb
select 1,'李斯','2009-10-12 16:40',11 union all
select 2,'王五','2009-10-12 16:41',12 union all
select 3,'成神','2009-10-12 16:42',13 union all
select 1,'李斯','2009-10-12 16:42',14 union all
select 1,'李斯','2009-11-12 16:42',15 union all
select 1,'李斯','2009-11-12 16:41',15

select * from @tb t
where not exists(select * from @tb where userid=t.userid and loginname<t.loginname and datediff(day,loginname,t.loginname)=0)
order by userid 


userid      username loginname               c4
----------- -------- ----------------------- -----------
1           李斯       2009-10-12 16:40:00.000 11
1           李斯       2009-11-12 16:41:00.000 15
2           王五       2009-10-12 16:41:00.000 12
3           成神       2009-10-12 16:42:00.000 13

(4 行受影响)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-17 15:32:15
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([userid] int,[username] varchar(4),[loginnanme] datetime,[C4] int)
insert [tb]
select 1,'李斯','2009-10-12 16:40',11 union all
select 2,'王五','2009-10-12 16:41',12 union all
select 3,'成神','2009-10-12 16:42',13 union all
select 1,'李斯','2009-10-12 16:42',14 union all
select 1,'李斯','2009-11-12 16:42',15 union all
select 1,'李斯','2009-11-12 16:41',15
--------------开始查询--------------------------

select * from [tb] t where  [loginnanme]=(select max([loginnanme]) from tb where [username]=t.[username]) order by 1
----------------结果----------------------------
/* userid      username loginnanme              C4
----------- -------- ----------------------- -----------
1           李斯       2009-11-12 16:42:00.000 15
2           王五       2009-10-12 16:41:00.000 12
3           成神       2009-10-12 16:42:00.000 13

(3 行受影响)


*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-17 15:32:15
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([userid] int,[username] varchar(4),[loginnanme] datetime,[C4] int)
insert [tb]
select 1,'李斯','2009-10-12 16:40',11 union all
select 2,'王五','2009-10-12 16:41',12 union all
select 3,'成神','2009-10-12 16:42',13 union all
select 1,'李斯','2009-10-12 16:42',14 union all
select 1,'李斯','2009-11-12 16:42',15 union all
select 1,'李斯','2009-11-12 16:41',15
--------------开始查询--------------------------

select
   * 
from 
  [tb] t 
where  
  [loginnanme]=(select max([loginnanme]) from tb where [username]=t.[username] and  datediff(dd,loginnanme,t.loginnanme)=0) 
order by 
  1
----------------结果----------------------------
/*userid      username loginnanme              C4
----------- -------- ----------------------- -----------
1           李斯       2009-10-12 16:42:00.000 14
1           李斯       2009-11-12 16:42:00.000 15
2           王五       2009-10-12 16:41:00.000 12
3           成神       2009-10-12 16:42:00.000 13

(4 行受影响)
*/

[icelovey]

declare @TB2 table([userid] int,[username] varchar(4),[loginnanme] datetime,[C4] int)
insert @TB2
select 1,'李斯','2009-10-12 16:40',11 union all
select 2,'王五','2009-10-12 16:41',12 union all
select 3,'成神','2009-10-12 16:42',13 union all
select 1,'李斯','2009-10-12 16:42',14 union all
select 1,'李斯','2009-11-12 16:42',15 union all
select 1,'李斯','2009-11-12 16:41',15

select * from @TB2

select * 
from @TB2 a 
where [loginnanme]=(select min([loginnanme]) from @TB2 where [username]=a.[username] and datediff(day,a.[loginnanme],[loginnanme])=0)
order by [userid]
/*
userid      username loginnanme              C4
----------- -------- ----------------------- -----------
1           李斯       2009-10-12 16:40:00.000 11
1           李斯       2009-11-12 16:41:00.000 15
2           王五       2009-10-12 16:41:00.000 12
3           成神       2009-10-12 16:42:00.000 13

(4 row(s) affected)
*/

[kasin000]

if OBJECT_ID('tb') is not null drop table tb
create table tb(userid int,username varchar(10),LoginTime Datetime)
Go
insert into  tb
select 1,'李斯','2009-10-12 16:40:11' union all
select 2,'王五','2009-10-12 16:41:12' union all
select 3,'成神','2009-10-12 16:42:13' union all
select 1,'李斯','2009-10-12 16:42:14' union all 
select 1,'李斯','2009-11-12 16:42:15' union all
select 1,'李斯','2009-11-12 16:41:15'

GO
with cte
as
(
select *,ROW_NUMBER()over(partition by UserName,Convert(varchar(10),LoginTime,120) order by LoginTime ) as pos from tb
)select cte.userid,cte.username,cte.LoginTime from cte where pos=1 order by userid



(6 行受影响)
userid      username   LoginTime
----------- ---------- -----------------------
1           李斯         2009-10-12 16:40:11.000
1           李斯         2009-11-12 16:41:15.000
2           王五         2009-10-12 16:41:12.000
3           成神         2009-10-12 16:42:13.000

(4 行受影响)

[qqhmitzk]

 select userid ,username,min(loginname)from @tb
 group by userid ,username,convert(varchar(10),loginname,120)
 order by userid

[zw365579102]

多谢各位