sql实现提取当前月份及当前月份后面5个月的数据，并有跨年度存在

[zbasic]

表结构说明
表中包含了某产品08-09年的出货记录，有月份和年份的字段。
需要得到的结果为显示本月份及本月份后面5个月份的数据。如果为9月份的话，将要在结果中添加2009年度
1月份和2月份的数据

table_1
order_id order_year m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12 prd_id
1 2008 1 2 3 4 5 6 7 8 9 10 11 12 prd_1
2 2009 11 22 33 44 55 66 0 0 0 0 0 0 prd_1


本月份为4月，那么需要的结果为
order_id prd_id 2008_m4 2008_m5 2008_m6 2008_m7 2008_m8 2008_m9
1 prd_id 4 5 6 7 8 9

如果本月份为9，那么需要的结果为
order_id 2008_m9 2008_m10 2008_m11 2008_m12 2008_m1 2009_m2
1 9 10 11 12 11 22

建表语句如下

create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))
insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)
 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'
union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'

[Limpire]

if object_id('table_1') is not null drop table table_1
create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))
insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)
 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'
union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'
go

declare @y smallint,@m tinyint,@sql nvarchar(4000)
select @y = 2008, @m = 9
set @sql = 'select order_id=min(order_id),prd_id=min(prd_id)'

select top 6 @sql = @sql+','+quotename(ltrim(y)+'_'+name)+'=max(case order_year when '+ltrim(y)+' then '+name+' else -1 end)' from
(
	select y=@y, m=colid-2, name from syscolumns where id = object_id('table_1') and colid between 3 and 14
	union all
	select y=@y+1,m=colid-2+12, name from syscolumns where id = object_id('table_1') and colid between 3 and 14
) t
where m >=@m

set @sql = @sql + ' from table_1 where order_year in (' + case when @y < 8 then ltrim(@y) else ltrim(@y)+','+ltrim(@y+1) end+')'
exec (@sql)

/*
order_id prd_id               2008_m9 2008_m10 2008_m11 2008_m12 2009_m1 2009_m2
-------- -------------------- ------- -------- -------- -------- ------- -------
1        prd_1                9       10       11       12       11      22
*/

if object_id('table_1') is not null drop table table_1

[zbasic]

很强悍的语句，需要研究一下，谢了。

[cxmcxm]

create table table_1(order_id smallint,order_year smallint,m1 smallint,m2 smallint,m3 smallint,m4 smallint,m5 smallint,m6 smallint,m7 smallint,m8 smallint,m9 smallint,m10 smallint,m11 smallint,m12 smallint,prd_id char(20))
insert into table_1(order_id,order_year,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,prd_id)
 select 1,2008,1,2,3,4,5,6,7,8,9,10,11,12,'prd_1'
union all  select 2,2009,11,22,33,44,55,66,0,0,0,0,0,0,'prd_1'

declare @year int,@month int
select @year=year(getdate()),@month=month(getdate())
--select @year=2008,@month=12
if @month>8 
	select order_id,order_year,
		m1=case when 1 between @month and @month+4 then m1 else 0 end,
		m2=case when 2 between @month and @month+4 then m2 else 0 end,
		m3=case when 3 between @month and @month+4 then m3 else 0 end,
		m4=case when 4 between @month and @month+4 then m4 else 0 end,
		m5=case when 5 between @month and @month+4 then m5 else 0 end,
		m6=case when 6 between @month and @month+4 then m6 else 0 end,
		m7=case when 7 between @month and @month+4 then m7 else 0 end,
		m8=case when 8 between @month and @month+4 then m8 else 0 end,
		m9=case when 9 between @month and @month+4 then m9 else 0 end,
		m10=case when 10 between @month and @month+4 then m10 else 0 end,
		m11=case when 11 between @month and @month+4 then m11 else 0 end,
		m12=case when 12 between @month and @month+4 then m12 else 0 end
	from table_1 where order_year=@year
	union
	select  order_id,order_year,
		m1=case when 1 between @month-12 and @month+4-12 then m1 else 0 end,
		m2=case when 2 between @month-12 and @month+4-12 then m2 else 0 end,
		m3=case when 3 between @month-12 and @month+4-12 then m3 else 0 end,
		m4=case when 4 between @month-12 and @month+4-12 then m4 else 0 end,
		m5=0,m6=0,m7=0,m8=0,m9=0,m10=0,m11=0,m12=0
	
	from table_1 where order_year=@year+1
else
	select  order_id,order_year,
		m1=case when 1 between @month and @month+4 then m1 else 0 end,
		m2=case when 2 between @month and @month+4 then m2 else 0 end,
		m3=case when 3 between @month and @month+4 then m3 else 0 end,
		m4=case when 4 between @month and @month+4 then m4 else 0 end,
		m5=case when 5 between @month and @month+4 then m5 else 0 end,
		m6=case when 6 between @month and @month+4 then m6 else 0 end,
		m7=case when 7 between @month and @month+4 then m7 else 0 end,
		m8=case when 8 between @month and @month+4 then m8 else 0 end,
		m9=case when 9 between @month and @month+4 then m9 else 0 end,
		m10=case when 10 between @month and @month+4 then m10 else 0 end,
		m11=case when 11 between @month and @month+4 then m11 else 0 end,
		m12=case when 12 between @month and @month+4 then m12 else 0 end
	from table_1 where order_year=@year
	
	

[zbasic]

谢谢cxmcxm ，Limpire ，相对来说，cxmcxm的好理解些，limpire的强悍些.结贴了

[Limpire]

呵呵，cxmcxm 的根本达不到你的要求。

永远返回12个月不说，你 set @month = 9 试试，2008和2009年肯定是在两行上的，不是在一行。

这样还不如直接看原始表好过，搞这么一大堆代码干什么，用肉眼去看6个月的数据，也不是太难。

[zbasic]

呵呵，开始没详细测试，大概看了一下，觉得基本上正确就没注意了。
确实是这样的。谢谢提醒。