27,580
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: @T1
declare @T1 table (num int,r1 int,r2 int,r3 int,r4 int,r5 int,r6 int)
insert into @T1
select 9001,4,21,23,24,30,31 union all
select 9002,10,14,17,25,29,33 union all
select 9003,2,3,6,15,25,30 union all
select 9004,3,11,13,17,28,31 union all
select 9005,1,3,8,15,17,21 union all
select 9006,6,12,18,20,26,33 union all
select 9007,1,5,12,23,25,26 union all
select 9008,4,15,16,22,32,33 union all
select 9009,8,15,21,30,31,33
declare @t2 table(num int ,value varchar(100))
insert into @t2
select num, rtrim(r1)+','+rtrim(r2)+','+rtrim(r3)+','+rtrim(r4)+','+rtrim(r5)+','+rtrim(r6)
from @t1
order by num desc
select * from @t2
select num
,r1 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r1)+',',','+value+',')>0 )
,r2 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r2)+',',','+value+',')>0)
,r3 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r3)+',',','+value+',')>0)
,r4 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r4)+',',','+value+',')>0)
,r5 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r5)+',',','+value+',')>0)
,r6 = (select top 1 a.num-1-num from @t2 where a.num >num and charindex(','+rtrim(r6)+',',','+value+',')>0)
from @T1 a
num r1 r2 r3 r4 r5 r6
----------- ----------- ----------- ----------- ----------- ----------- -----------
9001 NULL NULL NULL NULL NULL NULL
9002 NULL NULL NULL NULL NULL NULL
9003 NULL NULL NULL NULL 0 1
9004 0 NULL NULL 1 NULL 2
9005 NULL 0 NULL 1 0 3
9006 2 NULL NULL NULL NULL 3
9007 1 NULL 0 5 3 0
9008 6 2 NULL NULL NULL 1
9009 3 0 3 5 4 0
(所影响的行数为 9 行)
--楼主:比起你中彩票500W来说,这分也忒少了吧?
--楼主:这分也忒少了吧?