模板
教程
环境
性能
功能
管理
求记录中的最新数据的方法!
发表于：2007-07-02来源：作者：点击数：
标签：
已知数据: 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
