type 内容 时间
1 www 2010-01-10
2 bbb 2010-01-12
1 ccc 2010-01-14
1 eee 2010-01-20
2 fff 2010-01-11
1 ddd 2010-01-13
2 xxx 2010-01-18结果取出每个类别下的最新信息
type 内容 时间
1 eee 2010-01-20
2 xxx 2010-01-18
1 www 2010-01-10
2 bbb 2010-01-12
1 ccc 2010-01-14
1 eee 2010-01-20
2 fff 2010-01-11
1 ddd 2010-01-13
2 xxx 2010-01-18结果取出每个类别下的最新信息
type 内容 时间
1 eee 2010-01-20
2 xxx 2010-01-18
where 时间 = (select max(时间) from tb where type=t.type)
where not exists (select * from TableName where [type]=T.[Type] And T.[时间]<[时间])
*
from
tb t
where
时间 = (select max(时间) from tb where type=t.type)
-- Author :SQL77(只为思齐老)
-- Date :2010-01-22 14:14:20
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([type] int,[内容] varchar(3),[时间] datetime)
insert #TB
select 1,'www','2010-01-10' union all
select 2,'bbb','2010-01-12' union all
select 1,'ccc','2010-01-14' union all
select 1,'eee','2010-01-20' union all
select 2,'fff','2010-01-11' union all
select 1,'ddd','2010-01-13' union all
select 2,'xxx','2010-01-18'
--------------开始查询--------------------------select * from #TB T WHERE NOT EXISTS(SELECT 1 FROM #TB WHERE type=T.type AND 时间>T.时间)
----------------结果----------------------------
/* (所影响的行数为 7 行)type 内容 时间
----------- ---- ------------------------------------------------------
1 eee 2010-01-20 00:00:00.000
2 xxx 2010-01-18 00:00:00.000(所影响的行数为 2 行)
*/