表如下
id name year month
1 a 2011 5
2 b 2011 5
3 c 2011 5
4 a 2011 8
5 b 2011 8
6 c 2011 8
需要的结果为根据year和month条件求出不大于该条件的最大一组值
例如条件为year=2011 month=6
year*12+month<=2011*12+6
得出结果
1 a 2011 5
2 b 2011 5
3 c 2011 5
条件为year=2011 month=7
结果也是
1 a 2011 5
2 b 2011 5
3 c 2011 5
条件为year=2011 month=8
结果为
4 a 2011 8
5 b 2011 8
6 c 2011 8
谢谢大家了.
------解决方案--------------------------------------------------------
你这个可以简化啊 直接2011+6>year+month多好的
------解决方案--------------------------------------------------------
- SQL code
use Tempdbgo--> --> if not object_id(N'Tempdb..#A') is null drop table #AGoCreate table #A([id] int,[name] nvarchar(1),[year] int,[month] int)Insert #Aselect 0,N'a','2010',7 union allselect 1,N'a','2011',5 union allselect 2,N'b','2011',5 union allselect 3,N'c','2011',5 union allselect 4,N'a','2011',8 union allselect 5,N'b','2011',8 union allselect 6,N'c','2011',8GoDECLARE @year INT,@month INTSELECT @year=2011,@month=6SELECT [id],[name],[year],[month]FROM (Select * ,row=ROW_NUMBER()OVER(PARTITION BY [name] ORDER BY [year] DESC,[month] desc)from #A WHERE [year]<@year OR [year]=@year AND @month>=[month])TWHERE row=1/*id name year month1 a 2011 52 b 2011 53 c 2011 5*/
------解决方案--------------------------------------------------------
- SQL code
--/////////////////////小w//////////////////////////////////表如下id name year month1 a 2011 52 b 2011 53 c 2011 54 a 2011 85 b 2011 86 c 2011 8create table test_y( id int identity(1,1),name varchar(10),[year] datetime,[month]int)goinsert into test_y--select 'a' ,'2011',5select 'b' ,'2011',5 union allselect 'c' ,'2011',5 union allselect 'a' ,'2011',8 union allselect 'b' ,'2011',8 union allselect 'c' ,'2011',8select max(id) as id,name,max([year]) as [year],max([month])as [month] from test_y where datepart(yy,[year])=2011 and [month]<=8group by name--////////结果////////id name year month----------- ---------- ----------------------- -----------4 a 2011-01-01 00:00:00.000 85 b 2011-01-01 00:00:00.000 86 c 2011-01-01 00:00:00.000 8(3 行受影响)drop table test_y