求一条SELECT语句，有趣，常用，望高手解答

Question

有一张表
t_testid int4, 主健
user_id int4, 
visit_date timestamp要求输出id, user_id,visit_date ，但是user_id相同的记录并且visit_date里同一天的数据只输出当天时间最晚的一条,
而且， 排序是按 user_id 升序， visit_date 降序insert into t_test values(1, 13, timestamp '2007-4-12 09:30:21');
insert into t_test values(2, 13, timestamp '2007-4-12 12:31:32');
insert into t_test values(3, 13, timestamp '2007-4-12 23:11:00');
insert into t_test values(4, 13, timestamp '2007-5-23 07:41:42');
insert into t_test values(5, 15, timestamp '2007-4-12 23:51:52');
正确输出应该是
id user_id visit_date
4 13 2007-05-23 07:41:42
3 13 2007-04-12 23:11:00
5 15 2007-04-12 23:51:52谢谢

Answer 1

select 
    t.* 
from 
    t_test t 
where 
    not exists(select 1 from t_test where user_id=t.user_id and datediff(day,visit_date,t.visit_date)=0 and visit_date>t.visit_date)

Answer 2

select 
    t.* 
from 
    t_test t 
where 
    t.id=(select top 1 id from t_test where user_id=t.user_id and datediff(day,visit_date,t.visit_date)=0 order by visit_date desc)

Answer 3

select 
    t.* 
from 
    t_test t 
where 
    t.visit_date=(select max(visit_date) from t_test where user_id=t.user_id and datediff(day,visit_date,t.visit_date)=0)

Answer 4

create table #t(id int,[user_id] int,visit_date datetime)insert into #t values(1, 13, '2007-4-12 09:30:21');
insert into #t values(2, 13, '2007-4-12 12:31:32');
insert into #t values(3, 13, '2007-4-12 23:11:00');
insert into #t values(4, 13, '2007-5-23 07:41:42');
insert into #t values(5, 15, '2007-4-12 23:51:52');
select *
from #t AS a
where id = (select top 1 id from #t where datediff(d,visit_date,a.visit_date)=0 and [user_id]=a.user_id order by visit_date desc)
order by [user_id] ,visit_date descdrop table #t

Answer 5

create table #t(id int,[user_id] int,visit_date datetime)insert into #t values(1, 13, '2007-4-12 09:30:21');
insert into #t values(2, 13, '2007-4-12 12:31:32');
insert into #t values(3, 13, '2007-4-12 23:11:00');
insert into #t values(4, 13, '2007-5-23 07:41:42');
insert into #t values(5, 15, '2007-4-12 23:51:52');
select *
from #t AS a
where id = 
          (
                select top 1 id 
                from #t 
                where datediff(d,visit_date,a.visit_date)=0 
                     and [user_id]=a.user_id 
                order by visit_date desc)
order by [user_id] ,visit_date descdrop table #t

Answer 6

Select 
A.*
From
t_test A
Inner Join
(Select [user_id], Max(visit_date) As visit_date From t_test Group By [user_id]) B
On A.[user_id] = B.[user_id] And A.visit_date = B.visit_date
Order By [user_id], visit_date Desc

Answer 7

上面寫錯了一點，修改下
create table #t(id int,[user_id] int,visit_date datetime)insert into #t values(1, 13, '2007-4-12 09:30:21');
insert into #t values(2, 13, '2007-4-12 12:31:32');
insert into #t values(3, 13, '2007-4-12 23:11:00');
insert into #t values(4, 13, '2007-5-23 07:41:42');
insert into #t values(5, 15, '2007-4-12 23:51:52');Select 
A.*
From
#t A
Inner Join
(Select [user_id], Max(visit_date) As visit_date From #t Group By [user_id], Convert(Varchar(10), visit_date, 120) ) B
On A.[user_id] = B.[user_id] And A.visit_date = B.visit_date
Order By A.[user_id], A.visit_date DescGO
Drop Table #t

Answer 8

create table #t(id int,[user_id] int,visit_date datetime)insert into #t values(1, 13, '2007-4-12 09:30:21');
insert into #t values(2, 13, '2007-4-12 12:31:32');
insert into #t values(3, 13, '2007-4-12 23:11:00');
insert into #t values(4, 13, '2007-5-23 07:41:42');
insert into #t values(5, 15, '2007-4-12 23:51:52');Select 
A.*
From
#t A
Inner Join
(Select [user_id], Max(visit_date) As visit_date From #t Group By [user_id], Convert(Varchar(10), visit_date, 120) ) B
On A.[user_id] = B.[user_id] And A.visit_date = B.visit_date
Order By A.[user_id], A.visit_date DescGO
Drop Table #t
--Result
/*
id user_id visit_date
4 13 2007-05-23 07:41:42.000
3 13 2007-04-12 23:11:00.000
5 15 2007-04-12 23:51:52.000
*/

Answer 9

谢谢，楼上各位。我还想问下， 如果我要对上面的结果作count(*)如何能写的效率更高一点？正确结果应该是3条。

Answer 10

create table #t(id int,[user_id] int,visit_date datetime)insert into #t values(1, 13, '2007-4-12 09:30:21');
insert into #t values(2, 13, '2007-4-12 12:31:32');
insert into #t values(3, 13, '2007-4-12 23:11:00');
insert into #t values(4, 13, '2007-5-23 07:41:42');
insert into #t values(5, 15, '2007-4-12 23:51:52');Select 
Count(A.id) As Count
From
#t A
Inner Join
(Select [user_id], Max(visit_date) As visit_date From #t Group By [user_id], Convert(Varchar(10), visit_date, 120) ) B
On A.[user_id] = B.[user_id] And A.visit_date = B.visit_date
GO
Drop Table #t
--Result
/*
Count
3
*/