已知數據:
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
