27,579
社区成员
发帖
与我相关
我的任务
分享
create table [temp](ID int,uid int, time datetime)
insert into [temp] values(1 , 1 , '2009-9-26 11:57:20')
insert into [temp] values(2 , 1 , '2009-9-27 11:57:20')
insert into [temp] values(3 , 1 , '2009-9-21 11:57:20')
insert into [temp] values(4 , 2 , '2009-1-26 11:57:20')
insert into [temp] values(5 , 2 , '2009-2-27 11:57:20')
insert into [temp] values(6 , 3 , '2009-3-21 11:57:20')
go
select t.* from [temp] t where id = (select max(id) from [temp] where uid = t.uid) order by t.uid
select t.* from [temp] t where not exists (select 1 from [temp] where uid = t.uid and id > t.id) order by t.uid
drop table [temp]
/*
ID uid time
----------- ----------- ------------------------------------------------------
3 1 2009-09-21 11:57:20.000
5 2 2009-02-27 11:57:20.000
6 3 2009-03-21 11:57:20.000
(所影响的行数为 3 行)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-17 09:42:03
-- 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]([ID] int,[uid] int,[time] datetime)
insert [tb]
select 1,1,'2009-9-26 11:57:20' union all
select 2,1,'2009-9-27 11:57:20' union all
select 3,1,'2009-9-21 11:57:20' union all
select 4,2,'2009-1-26 11:57:20' union all
select 5,2,'2009-2-27 11:57:20' union all
select 6,3,'2009-3-21 11:57:20'
--------------开始查询--------------------------
select * from tb a where time=(select max(time) from tb where uid=a.uid) order by 2
----------------结果----------------------------
/* ID uid time
----------- ----------- -----------------------
2 1 2009-09-27 11:57:20.000
5 2 2009-02-27 11:57:20.000
6 3 2009-03-21 11:57:20.000
(3 行受影响)
*/
select t.* from temp t where id = (select max(id) from temp where uid = t.uid)
select t.* from temp t where not exists (select 1 from temp where uid = t.uid and id > t.id)
select *
from tb t
where
id=(select top 1 id from tb where uid=t.uid order by id desc)
select * from tb t where not exists(select 1 from tb where uid=t.uid and id>t.id)