关于某个bh的最后一条记录

[crazy_boom]

sql2008R2

create table #t
(bh char(10) ,
 rq char(10),
 dh char(10),
 sl int ,
 dj decimal(14,2)
) 
insert into #t
select '101','2016-01-01','xes00001',10,15.2
union
select '101','2016-02-01','xes00002',5,12.2
union
select '101','2016-02-11','xes00003',10,16.2
union
select '102','2016-03-01','xes00004',10,18.00
union
select '102','2016-05-01','xes00005',10,23.2
union
select '103','2016-06-01','xes00006',10,25.2
union
select '103','2016-07-01','xes00007',10,33.2

select * from #t
--获取每个BH最后的一次销售记录
select a.* 
 from #t a join (select bh,MAX(dh) as dh from #t group by bh) b on a.bh=b.bh and a.dh=b.dh 

除了我写的那个方法 还有别的更简单的方法吗

[唐诗三百首]

select * 
 from #t a
 where not exists(select 1 from #t b where b.bh=a.bh and b.dh>a.dh)

[道素]

也算是一种方法

SELECT * FROM (
    SELECT *,COUNT(0)OVER(PARTITION BY bh) AS line_count,ROW_NUMBER()OVER(PARTITION BY bh ORDER BY rq desc) AS rn
   FROM #t
) t WHERE t.line_count=t.rn
/*
bh         rq         dh         sl          dj                                      line_count  rn
---------- ---------- ---------- ----------- --------------------------------------- ----------- --------------------
101        2016-01-01 xes00001   10          15.20                                   3           3
102        2016-03-01 xes00004   10          18.00                                   2           2
103        2016-06-01 xes00006   10          25.20                                   2           2
*/

[中国风]

SELECT *
 FROM   ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY bh ORDER BY dh DESC ) AS RN
          FROM      #t
        ) AS t
 WHERE  RN = 1;