declare @t table(x int,x1 nvarchar(10),x2 nvarchar(100) )
declare @t1 table(xid int,x3 int )
declare @t2 table(x5 nvarchar(100) )
declare @a int, @b int
set @a=1
set @b=0
insert into @t(x,x1,x2)
select 1,'-N天','datediff(d,getdate(),tuizuDate) <-30' union
select 2,'-30天','datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15' union
select 3,'-15天','datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7' union
select 4,'-7天','datediff(d,getdate(),tuizuDate) >=-7 and datediff(d,getdate(),tuizuDate) <-3' union
select 5,'-3天','datediff(d,getdate(),tuizuDate) >=-3 and datediff(d,getdate(),tuizuDate) <0' union
select 6,'15天','datediff(d,getdate(),tuizuDate) >=0 and datediff(d,getdate(),tuizuDate) <15' union
select 7,'30天','datediff(d,getdate(),tuizuDate) >=15 and datediff(d,getdate(),tuizuDate) <30' union
select 8,'60天','datediff(d,getdate(),tuizuDate) >=30 and datediff(d,getdate(),tuizuDate) <60' union
select 9,'90天','datediff(d,getdate(),tuizuDate) >=60 and datediff(d,getdate(),tuizuDate) <90' union
select 10,'180天','datediff(d,getdate(),tuizuDate) >=90 and datediff(d,getdate(),tuizuDate) <180' union
select 11,'270天','datediff(d,getdate(),tuizuDate) >=180 and datediff(d,getdate(),tuizuDate) <270' union
select 12,'N天','datediff(d,getdate(),tuizuDate)>270'
insert into @t1 values(0,0)
insert into @t1 values(1,10)
insert into @t1 values(2,20)
insert into @t1 values(3,30)
insert into @t1 values(4,40)
insert into @t1 values(5,50)
insert into @t1 values(6,60)
insert into @t1 values(7,70)
insert into @t1 values(8,80)
insert into @t1 values(9,90)
insert into @t1 values(10,100)
while(@a<=12)
begin
while(@b<=10)
begininsert into @t2
select convert(nvarchar(10),(select x3 from @t1 where xid=@b))+
'_'+(select x2 from @t where x=@a)set @b=@b+1
endset @a=@a+1
end
select * from @t2/* 打印的结果为
0_datediff(d,getdate(),tuizuDate) <-30
10_datediff(d,getdate(),tuizuDate) <-30
20_datediff(d,getdate(),tuizuDate) <-30
30_datediff(d,getdate(),tuizuDate) <-30
40_datediff(d,getdate(),tuizuDate) <-30
50_datediff(d,getdate(),tuizuDate) <-30
60_datediff(d,getdate(),tuizuDate) <-30
70_datediff(d,getdate(),tuizuDate) <-30
80_datediff(d,getdate(),tuizuDate) <-30
90_datediff(d,getdate(),tuizuDate) <-30
100_datediff(d,getdate(),tuizuDate) <-30*//* 我想要的结果
0_datediff(d,getdate(),tuizuDate) <-30
10_datediff(d,getdate(),tuizuDate) <-30
20_datediff(d,getdate(),tuizuDate) <-30
30_datediff(d,getdate(),tuizuDate) <-30
40_datediff(d,getdate(),tuizuDate) <-30
50_datediff(d,getdate(),tuizuDate) <-30
60_datediff(d,getdate(),tuizuDate) <-30
70_datediff(d,getdate(),tuizuDate) <-30
80_datediff(d,getdate(),tuizuDate) <-30
90_datediff(d,getdate(),tuizuDate) <-30
100_datediff(d,getdate(),tuizuDate) <-300_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
10_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
20_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
30_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
40_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
50_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
60_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
70_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
80_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
90_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15
100_datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-150_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
10_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
20_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
30_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
40_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
50_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
60_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
70_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
80_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
90_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7
100_datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7......*/说通俗点 就是 要 循环 @a * @b 的次数 也就是 132 次 , 结果 也根据 @t 和 @t1 表 交叉打印的 ,不是固定的
但是 我循环到 11 次就 不走了, 不知道为什么 , 望高手指点一下...
select @a=@a+1, @b=0
--不用循环,你看行不行?
declare @t table(x int,x1 nvarchar(10),x2 nvarchar(100) )
declare @t1 table(xid int,x3 int )
declare @t2 table(x4 nvarchar(10),x5 nvarchar(100) )
declare @a int, @b int
set @a=1
set @b=0
insert into @t(x,x1,x2)
select 1,'-N天','datediff(d,getdate(),tuizuDate) <-30' union
select 2,'-30天','datediff(d,getdate(),tuizuDate) >=-30 and datediff(d,getdate(),tuizuDate) <-15' union
select 3,'-15天','datediff(d,getdate(),tuizuDate) >=-15 and datediff(d,getdate(),tuizuDate) <-7' union
select 4,'-7天','datediff(d,getdate(),tuizuDate) >=-7 and datediff(d,getdate(),tuizuDate) <-3' union
select 5,'-3天','datediff(d,getdate(),tuizuDate) >=-3 and datediff(d,getdate(),tuizuDate) <0' union
select 6,'15天','datediff(d,getdate(),tuizuDate) >=0 and datediff(d,getdate(),tuizuDate) <15' union
select 7,'30天','datediff(d,getdate(),tuizuDate) >=15 and datediff(d,getdate(),tuizuDate) <30' union
select 8,'60天','datediff(d,getdate(),tuizuDate) >=30 and datediff(d,getdate(),tuizuDate) <60' union
select 9,'90天','datediff(d,getdate(),tuizuDate) >=60 and datediff(d,getdate(),tuizuDate) <90' union
select 10,'180天','datediff(d,getdate(),tuizuDate) >=90 and datediff(d,getdate(),tuizuDate) <180' union
select 11,'270天','datediff(d,getdate(),tuizuDate) >=180 and datediff(d,getdate(),tuizuDate) <270' union
select 12,'N天','datediff(d,getdate(),tuizuDate)>270'
insert into @t1 values(0,0)
insert into @t1 values(1,10)
insert into @t1 values(2,20)
insert into @t1 values(3,30)
insert into @t1 values(4,40)
insert into @t1 values(5,50)
insert into @t1 values(6,60)
insert into @t1 values(7,70)
insert into @t1 values(8,80)
insert into @t1 values(9,90)
insert into @t1 values(10,100)
insert into @t2
select x3,x2 from @t,@t1 order by x2
--select * from @t2
select rtrim(ltrim(x4))+'_'+rtrim(ltrim(x5)) from @t2
begin
set @b=0while(@b<=10)
begin