原来的表
col1 col2 col3
aa 5 2013-02-15 14:47:11
aa 7 2013-02-16 15:12:23
aa 6 2013-02-17 16:21:55
bb 9 2013-02-15 14:47:11
bb 7 2013-02-16 15:12:23
bb 6 2013-02-19 16:21:42
bb 3 2013-02-15 14:47:11
cc 1 2013-02-17 15:12:23
cc 8 2013-02-17 16:21:57
dd 6 2013-02-18 09:21:23
需要得到表中记录 获取的条件就是 col3中 时间最大的记录。
col1 col2 col3
aa 6 2013-02-17 16:21:55
bb 6 2013-02-19 16:21:42
cc 8 2013-02-17 16:21:57
dd 6 2013-02-18 09:21:23
------解决方案--------------------
select * from 表 a where not exists (select 1 from 表 where col1=a.col1 and a.col3<col3)
------解决方案--------------------
select * from tb a where exists
(select 1 from tb where col1=a.col1 group by col1 having max(col3)=a.col3)
------解决方案--------------------
select * from 表 as a where not exists(select * from 表 where col1=a.col1 and col3>a.col3 )
------解决方案--------------------
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-02-18 16:29:34
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([col1] varchar(2),[col2] int,[col3] datetime)