select * from tb t where not exists(select 1 from tb where 编号=t.编号 AND 日期>t.日期)
select * from ( select *, ROW_NUMBER() over (partition by [编号] order by [日期] desc) as rn from [表名] ) t where rn =1
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-05-30 14:50:42 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([编号] varchar(3),[类型] varchar(5),[类别] varchar(2),[日期] datetime) insert [tb] select '012','普通2','A1','2014-04-15 00:00:00' union all select '012','普通1','A1','2014-05-08 00:00:00' union all select '013','普通1','A1','2014-04-21 00:00:00' union all select '013','普通1','A1','2014-05-22 00:00:00' union all select '014','普通2','A3','2014-05-18 00:00:00' --------------开始查询-------------------------- select * from tb t where not exists(select 1 from tb where 编号=t.编号 AND 日期>t.日期)select * from tb t where 日期=(select max(日期) from tb where 编号=t.编号) ----------------结果---------------------------- /* 编号 类型 类别 日期 ---- ----- ---- ----------------------- 012 普通1 A1 2014-05-08 00:00:00.000 013 普通1 A1 2014-05-22 00:00:00.000 014 普通2 A3 2014-05-18 00:00:00.000 */
select * from
(
select *, ROW_NUMBER() over (partition by [编号] order by [日期] desc) as rn
from [表名]
) t where rn =1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-05-30 14:50:42
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(3),[类型] varchar(5),[类别] varchar(2),[日期] datetime)
insert [tb]
select '012','普通2','A1','2014-04-15 00:00:00' union all
select '012','普通1','A1','2014-05-08 00:00:00' union all
select '013','普通1','A1','2014-04-21 00:00:00' union all
select '013','普通1','A1','2014-05-22 00:00:00' union all
select '014','普通2','A3','2014-05-18 00:00:00'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where 编号=t.编号 AND 日期>t.日期)select * from tb t where 日期=(select max(日期) from tb where 编号=t.编号)
----------------结果----------------------------
/* 编号 类型 类别 日期
---- ----- ---- -----------------------
012 普通1 A1 2014-05-08 00:00:00.000
013 普通1 A1 2014-05-22 00:00:00.000
014 普通2 A3 2014-05-18 00:00:00.000
*/