求记录中的最新数据的方法!_sqlserver

已知数据:

Column1    Column2     Column3    Column4A                      10                     am         1999-01-01 00:00:00.000A                      11                     am         1999-01-02 00:00:00.000B                     12                      bm         1999-01-03 00:00:00.000B                     13                      bm         1999-01-04 00:00:00.000C                    14                      cm         1999-01-05 00:00:00.000C                     15                     cm         1999-01-06 00:00:00.000

要求得到数据:

http://www.gaodaima.com/34591.html求记录中的最新数据的方法!_sqlserver

Column1    Column2     Column3    Column4A                      11                     am         1999-01-02 00:00:00.000B                     13                      bm         1999-01-04 00:00:00.000C                     15                     cm         1999-01-06 00:00:00.000

–数据装载

Create Table #T(Column1 varchar(10),Column2 int,Column3 varchar(10),Column4 datetime)insert #T select ‘A’,10,’am’,’1999-1-1’union all select ‘A’,11,’am’,’1999-1-2’union all select ‘B’,12,’bm’,’1999-1-3’union all select ‘B’,13,’bm’,’1999-1-4’union all select ‘C’,14,’cm’,’1999-1-5’union all select ‘C’,15,’cm’,’1999-1-6′

–测试语句 方法1：

select a.* from #T a where (a.Column4) =    (select top 1 (Column4) from #T where Column1 = a.Column1 order by Column4 desc)

–测试结果:

Column1    Column2     Column3    Column4                                                ———- ———– ———- —————————————————— A          11          am         1999-01-02 00:00:00.000B          13          bm         1999-01-04 00:00:00.000C          15          cm         1999-01-06 00:00:00.000

–测试语句 方法2:(最佳效率)

select a.* from #T a join( select Column1,Column4=max(Column4) from #T group by Column1 )b on a.Column1=b.Column1 and a.Column4=b.Column4order by a.Column1

–测试结果 方法2:

Column1    Column2     Column3    Column4                                                ———- ———– ———- —————————————————— A          11          am         1999-01-02 00:00:00.000B          13          bm         1999-01-04 00:00:00.000C          15          cm         1999-01-06 00:00:00.000

欢迎大家阅读《求记录中的最新数据的方法!_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)