id userID Username logintime
----------- ------ -------------------- ------------------------------------------------------
1 aaaa aaaa 2007-01-01 10:20:30.000
2 bbbb bbbb 2007-01-01 10:20:30.000
3 cccc cccc 2007-01-01 10:20:30.000
4 aaaa aaaa 2007-01-01 10:30:40.000
5 bbbb bbbb 2007-01-01 10:30:40.000
6 cccc cccc 2007-01-01 10:30:40.000
7 aaaa aaaa 2007-01-02 10:30:40.000
8 bbbb bbbb 2007-01-02 10:30:40.000
9 cccc cccc 2007-01-02 11:30:40.000
10 aaaa aaaa 2007-01-02 11:40:50.000
11 bbbb bbbb 2007-01-02 11:40:50.000
12 cccc cccc 2007-01-02 11:40:50.000
13 aaaa aaaa 2007-01-03 11:40:50.000
14 bbbb bbbb 2007-01-03 11:40:50.000
15 cccc cccc 2007-01-03 11:40:50.000
16 aaaa aaaa 2007-01-03 12:10:20.000
17 bbbb bbbb 2007-01-03 12:10:20.000
18 cccc cccc 2007-01-03 12:10:20.000
把所有用户每天最后一次登陆的结果显示出来。
想得到如下显示结果：
id userID Username logintime
----------- ------ -------------------- ------------------------------------------------------
4 aaaa aaaa 2007-01-01 10:30:40.000
5 bbbb bbbb 2007-01-01 10:30:40.000
6 cccc cccc 2007-01-01 10:30:40.000
10 aaaa aaaa 2007-01-02 11:40:50.000
11 bbbb bbbb 2007-01-02 11:40:50.000
12 cccc cccc 2007-01-02 11:40:50.000
16 aaaa aaaa 2007-01-03 12:10:20.000
17 bbbb bbbb 2007-01-03 12:10:20.000
18 cccc cccc 2007-01-03 12:10:20.000
9 个解决方案
#1
select * from tab a where not exists (select 1 from tab where userID=a.userID and logintime >a.logintime )
#2
我要所有的啊，这个只是最后一天的的登陆记录，以前的记录还是不能得到。
#3
try:
select * from tab a where not exists (select 1 from tab where userID=a.userID and
datediff(dd,logintime,a.logintime)=0 and logintime >a.logintime )
#4
select userID, date=convert(varchar(10),logintime,102) , time = max(convert(varchar(10),logintime,108))
from tab
group by userID, convert(varchar(10),logintime,102)
#5
declare @tb table (id int,userid varchar(20),username varchar(20),logintime datetime)
insert into @tb select 1,'aaaa','aaaa','2007-01-01 10:20:30.000'
insert into @tb select 2,'bbbb','bbbb','2007-01-01 10:20:30.000'
insert into @tb select 3,'cccc','cccc','2007-01-01 10:20:30.000'
insert into @tb select 4,'aaaa','aaaa','2007-01-01 10:20:40.000'
insert into @tb select 5,'bbbb','bbbb','2007-01-01 10:20:40.000'
insert into @tb select 6,'cccc','cccc','2007-01-01 10:20:40.000'
insert into @tb select 7,'aaaa','aaaa','2007-01-02 10:20:30.000'
insert into @tb select 8,'bbbb','bbbb','2007-01-02 10:20:30.000'
insert into @tb select 9,'cccc','cccc','2007-01-02 10:20:30.000'
insert into @tb select 10,'aaaa','aaaa','2007-01-02 10:20:40.000'
insert into @tb select 11,'bbbb','bbbb','2007-01-02 10:20:40.000'
insert into @tb select 12,'cccc','cccc','2007-01-02 10:20:40.000'
insert into @tb select 13,'aaaa','aaaa','2007-01-03 10:20:30.000'
insert into @tb select 14,'bbbb','bbbb','2007-01-03 10:20:30.000'
insert into @tb select 15,'cccc','cccc','2007-01-03 10:20:30.000'
insert into @tb select 16,'aaaa','aaaa','2007-01-03 10:20:40.000'
insert into @tb select 17,'bbbb','bbbb','2007-01-03 10:20:40.000'
insert into @tb select 18,'cccc','cccc','2007-01-03 10:20:40.000'
select * from @tb a where
not exists
(select 1 from @tb
where userID=a.userID
and convert(varchar(10),logintime,120)=convert(varchar(10),a.logintime,120)
and logintime >a.logintime )
id userid username logintime
4 aaaa aaaa 2007-01-01 10:20:40.000
5 bbbb bbbb 2007-01-01 10:20:40.000
6 cccc cccc 2007-01-01 10:20:40.000
10 aaaa aaaa 2007-01-02 10:20:40.000
11 bbbb bbbb 2007-01-02 10:20:40.000
12 cccc cccc 2007-01-02 10:20:40.000
16 aaaa aaaa 2007-01-03 10:20:40.000
17 bbbb bbbb 2007-01-03 10:20:40.000
18 cccc cccc 2007-01-03 10:20:40.000
#6
3楼对
#7
#8
再问个问题，怎么给分？？-.-!
#9
想学习下，自己也遇到了
#1
select * from tab a where not exists (select 1 from tab where userID=a.userID and logintime >a.logintime )
#2
我要所有的啊，这个只是最后一天的的登陆记录，以前的记录还是不能得到。
#3
try:
select * from tab a where not exists (select 1 from tab where userID=a.userID and
datediff(dd,logintime,a.logintime)=0 and logintime >a.logintime )
#4
select userID, date=convert(varchar(10),logintime,102) , time = max(convert(varchar(10),logintime,108))
from tab
group by userID, convert(varchar(10),logintime,102)
#5
declare @tb table (id int,userid varchar(20),username varchar(20),logintime datetime)
insert into @tb select 1,'aaaa','aaaa','2007-01-01 10:20:30.000'
insert into @tb select 2,'bbbb','bbbb','2007-01-01 10:20:30.000'
insert into @tb select 3,'cccc','cccc','2007-01-01 10:20:30.000'
insert into @tb select 4,'aaaa','aaaa','2007-01-01 10:20:40.000'
insert into @tb select 5,'bbbb','bbbb','2007-01-01 10:20:40.000'
insert into @tb select 6,'cccc','cccc','2007-01-01 10:20:40.000'
insert into @tb select 7,'aaaa','aaaa','2007-01-02 10:20:30.000'
insert into @tb select 8,'bbbb','bbbb','2007-01-02 10:20:30.000'
insert into @tb select 9,'cccc','cccc','2007-01-02 10:20:30.000'
insert into @tb select 10,'aaaa','aaaa','2007-01-02 10:20:40.000'
insert into @tb select 11,'bbbb','bbbb','2007-01-02 10:20:40.000'
insert into @tb select 12,'cccc','cccc','2007-01-02 10:20:40.000'
insert into @tb select 13,'aaaa','aaaa','2007-01-03 10:20:30.000'
insert into @tb select 14,'bbbb','bbbb','2007-01-03 10:20:30.000'
insert into @tb select 15,'cccc','cccc','2007-01-03 10:20:30.000'
insert into @tb select 16,'aaaa','aaaa','2007-01-03 10:20:40.000'
insert into @tb select 17,'bbbb','bbbb','2007-01-03 10:20:40.000'
insert into @tb select 18,'cccc','cccc','2007-01-03 10:20:40.000'
select * from @tb a where
not exists
(select 1 from @tb
where userID=a.userID
and convert(varchar(10),logintime,120)=convert(varchar(10),a.logintime,120)
and logintime >a.logintime )
id userid username logintime
4 aaaa aaaa 2007-01-01 10:20:40.000
5 bbbb bbbb 2007-01-01 10:20:40.000
6 cccc cccc 2007-01-01 10:20:40.000
10 aaaa aaaa 2007-01-02 10:20:40.000
11 bbbb bbbb 2007-01-02 10:20:40.000
12 cccc cccc 2007-01-02 10:20:40.000
16 aaaa aaaa 2007-01-03 10:20:40.000
17 bbbb bbbb 2007-01-03 10:20:40.000
18 cccc cccc 2007-01-03 10:20:40.000
#6
3楼对
#7
#8
再问个问题，怎么给分？？-.-!
#9
想学习下，自己也遇到了