select * from Journal where AdAgStart < 45 and AdAgEnd >=45 and AdMon=12
select * from Journal where AdAgStart < 46 and AdAgEnd >=46 and AdMon=1
select * from Journal where AdAgStart < 47 and AdAgEnd >=47 and AdMon=2
select * from Journal where AdAgStart < 48 and AdAgEnd >=48 and AdMon=3
select * from Journal where AdAgStart < 49 and AdAgEnd >=49 and AdMon=4
select * from Journal where AdAgStart < 50 and AdAgEnd >=50 and AdMon=545,46,47,48,49,50是递增的。知道45知道循环6次得出来的结果就是45,46,47,48,49,5012,1,2,3,4,5 也是递增,是月份的递增
这样的存储过程 应该怎么写啊?高手帮助
@AdAg int
as
declare @m int
set @m = month(getdate())
declare @i int
set @i = 0
while @i <= 5
begin
select * from Journal where AdAgStart < @AdAg + @i and AdAgEnd >= @AdAg + @i and AdMon= @m
set @m = @m + 1
if @m > 12
set @m = 1
set @i = @i + 1
end
go-调用
exec pr_test 45
--传来数据
declare @i int,@m int
set @i=45
set @m=12
--查询
select * from Journal where AdAgStart < @i and AdAgEnd >=@i+6 and AdMon=(AdAgStart-@i-2+@m)%12+1
select @x=45,@y=6
select @y=@x+@y-1,@m=month(getdate()),@sql=''
while(@x<=@y)
begin
select @sql=@sql+'select * from Journal where AdAgStart<'+cast(@x as varchar(5))
+' and AdAgEnd>='+cast(@x as varchar(5))+' and AdMon='+cast(@m as varchar(5))+';'
select @x=@x+1,@m=case when @m+1=13 then 1 else @m+1 end
end-- 执行@sql
exec(@sql)-- 打印@sql
print @sql--> 结果
select * from Journal where AdAgStart<45 and AdAgEnd>=45 and AdMon=12;
select * from Journal where AdAgStart<46 and AdAgEnd>=46 and AdMon=1;
select * from Journal where AdAgStart<47 and AdAgEnd>=47 and AdMon=2;
select * from Journal where AdAgStart<48 and AdAgEnd>=48 and AdMon=3;
select * from Journal where AdAgStart<49 and AdAgEnd>=49 and AdMon=4;
select * from Journal where AdAgStart<50 and AdAgEnd>=50 and AdMon=5;
DROP TABLE [Journal]CREATE TABLE [Journal] (
[JId] [int] IDENTITY (1, 1) NOT NULL,
[Name] [varchar] (100) NULL,
[Num] [varchar] (50) NULL,
[AdAgStart] [int] NULL,
[AdAgEnd] [int] NULL,
[AdMon] [int] NULL,
[Stock] [int] NULL,
[Time] [datetime] NULL DEFAULT (getdate()),
[Rem] [varchar] (500) NULL)ALTER TABLE [Journal] WITH NOCHECK ADD CONSTRAINT [PK_Journal] PRIMARY KEY NONCLUSTERED ( [JId] )
SET IDENTITY_INSERT [Journal] ONINSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 1,N'2012年3-4岁第一期',N'20123401',36,48,1,0,N'2011-12-25 14:07:31')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 2,N'2012年3-4岁第二期',N'20123402',36,48,2,0,N'2011-12-26 14:07:31')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 3,N'2012年3-4岁第三期',N'20123403',36,48,3,0,N'2011-12-26 14:07:46')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 4,N'2012年3-4岁第四期',N'20123404',36,48,4,0,N'2011-12-26 14:07:50')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 5,N'2012年3-4岁第五期',N'20123405',36,48,5,0,N'2011-12-26 14:07:53')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 6,N'2012年3-4岁第六期',N'20123406',36,48,6,0,N'2011-12-26 14:07:56')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 7,N'2012年3-4岁第七期',N'20123407',36,48,7,0,N'2011-12-26 14:08:02')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 8,N'2012年3-4岁第八期',N'20123408',36,48,8,0,N'2011-12-26 14:08:11')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 9,N'2012年3-4岁第九期',N'20123409',36,48,9,0,N'2011-12-26 14:08:16')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 10,N'2012年3-4岁第十期',N'201234010',36,48,10,0,N'2011-12-26 14:08:21')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 11,N'2012年3-4岁第十一期',N'201234011',36,48,11,0,N'2011-12-26 14:08:26')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 12,N'2012年3-4岁第十二期',N'201234012',36,48,12,0,N'2011-12-26 14:08:32')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 13,N'2012年4-5岁第一期',N'20124501',48,60,1,0,N'2011-12-26 14:30:19')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 14,N'2012年4-5岁第二期',N'20124502',48,60,2,0,N'2011-12-26 14:30:20')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 15,N'2012年4-5岁第三期',N'20124503',48,60,3,0,N'2011-12-26 14:30:24')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 16,N'2012年4-5岁第四期',N'20124504',48,60,4,0,N'2011-12-26 14:30:24')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 17,N'2012年4-5岁第五期',N'20124505',48,60,5,0,N'2011-12-26 14:30:25')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 18,N'2012年4-5岁第六期',N'20124506',48,60,6,0,N'2011-12-26 14:30:25')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 19,N'2012年4-5岁第七期',N'20124507',48,60,7,0,N'2011-12-26 14:30:26')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 20,N'2012年4-5岁第八期',N'20124508',48,60,8,0,N'2011-12-26 14:30:27')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 21,N'2012年4-5岁第九期',N'20124509',48,60,9,0,N'2011-12-26 14:30:27')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 22,N'2012年4-5岁第十期',N'201245010',48,60,10,0,N'2011-12-26 14:30:28')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 23,N'2012年4-5岁第十一期',N'201245011',48,60,11,0,N'2011-12-26 14:30:29')
INSERT [Journal] ([JId],[Name],[Num],[AdAgStart],[AdAgEnd],[AdMon],[Stock],[Time]) VALUES ( 24,N'2012年4-5岁第十二期',N'201245012',48,60,12,0,N'2011-12-26 14:30:31')SET IDENTITY_INSERT [Journal] OFF
select @x=45,@y=6
select @y=@x+@y-1,@m=month(getdate()),@sql=''
while(@x<=@y)
begin
select @sql=@sql+'select * from Journal where AdAgStart<'+cast(@x as varchar(5))
+' and AdAgEnd>='+cast(@x as varchar(5))+' and AdMon='+cast(@m as varchar(5))
+case when @x=@y then ' ' else ' union all ' end
select @x=@x+1,@m=case when @m+1=13 then 1 else @m+1 end
end-- 执行@sql
exec(@sql)-- 打印@sql
print @sql--> 结果
select * from Journal where AdAgStart<45 and AdAgEnd>=45 and AdMon=12 union all
select * from Journal where AdAgStart<46 and AdAgEnd>=46 and AdMon=1 union all
select * from Journal where AdAgStart<47 and AdAgEnd>=47 and AdMon=2 union all
select * from Journal where AdAgStart<48 and AdAgEnd>=48 and AdMon=3 union all
select * from Journal where AdAgStart<49 and AdAgEnd>=49 and AdMon=4 union all
select * from Journal where AdAgStart<50 and AdAgEnd>=50 and AdMon=5