SQLserver以最大值为条件查询问题

Question

CREATE TABLE #TEST
(ITEM VARCHAR(100),  --费用
 MON CHAR(2),  --月份
 value decimal(10,2))   --金额
 go
 insert into #TEST values('办公费','01',5000)
 insert into #TEST values('办公费','02',3000)
 insert into #TEST values('办公费','03',2000)
 insert into #TEST values('办公费','04',3300)
 insert into #TEST values('办公费','05',1800)
 insert into #TEST values('差旅费','01',10000)
 insert into #TEST values('差旅费','02',21000)
 insert into #TEST values('差旅费','03',30000)
 insert into #TEST values('差旅费','04',29000)
 insert into #TEST values('差旅费','05',17000)
 go
--取月份范围内最大月份的数据，如2-4月份(这个范围是变量，在程序中由客户自己选择)的就取4月份的数据

--以下SQL没实现

 select * from #test where (mon  between '02' and '04') and mon=(select max(mon) maxmon from #test a where a.ITEM=#test.ITEM and a.mon=#test.mon) 

 这个SQL该怎么写？

Answer 1

SELECT t.* FROM (
SELECT *,DENSE_RANK() OVER(ORDER BY mon DESC) as n
FROM #test 
WHERE(mon  BETWEEN '02' and '04')
) AS t
WHERE t.n = 1;