求记录中的最新数据的方法!
发布时间：2018-08-18 02:27:40 所属栏目：站长百科 来源：站长网
导读：已知数据: 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 要求得到
|
已知数据: 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 要求得到数据: 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 （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
