select k, (select top 1 j from t b where b.k=a.k order by r desc ) j from (select distinct k from t)a
select * from 表 t where not exists(select 1 from 表 where [科目]=t.[科目] and [日期]>t.[日期])
declare @tb1 table (item varchar(10),[date] char(8),[money] decimal) insert into @tb1 select '001', '20050617', 10 union all select '002', '20050617', 20 union all select '003', '20050617', 33 union all select '001', '20050618', 11 union all select '002', '20050618', 21 union all select '001', '20050619', 12 union all select '003', '20050619', 32 union all select '001', '20050620', 13 union all select '002', '20050620', 22 union all select '003', '20050620', 31select t.item,t.[date],u.[money] from ( select top 100 percent item,[date]=max([date]) from @tb1 group by item order by item)t inner join @tb1 u on t.item=u.item and t.[date]=u.[date]/* (所影响的行数为 10 行)item date money ---------- -------- -------------------- 001 20050620 13 002 20050620 22 003 20050620 31(所影响的行数为 3 行) */
from (select distinct k from t)a
where not exists(select 1 from 表 where [科目]=t.[科目] and [日期]>t.[日期])
insert into @tb1
select '001', '20050617', 10 union all
select '002', '20050617', 20 union all
select '003', '20050617', 33 union all
select '001', '20050618', 11 union all
select '002', '20050618', 21 union all
select '001', '20050619', 12 union all
select '003', '20050619', 32 union all
select '001', '20050620', 13 union all
select '002', '20050620', 22 union all
select '003', '20050620', 31select t.item,t.[date],u.[money] from (
select top 100 percent item,[date]=max([date]) from @tb1 group by item order by item)t
inner join @tb1 u on t.item=u.item and t.[date]=u.[date]/*
(所影响的行数为 10 行)item date money
---------- -------- --------------------
001 20050620 13
002 20050620 22
003 20050620 31(所影响的行数为 3 行)
*/