求一条单表查询的复杂SQL语句？解决办法

求一条单表查询的复杂SQL语句？？
表T1，有两个字段 fsNums,fsAlreadyNums

假设有如下两条记录
fsNums 1001,1002,1003-1008
fsAlreadyNums 1001

fsNums 1009，1010-1020
fsAlreadyNums 1015

假设：现有一个未知编号，假如是：1002
因为1002，属于第一条记录fsNums字段所包含的值，要写一条Sql语句，更改第一条语句fsAlradyNums的值为1001,1002

假设：现有一个未知编号，假如是：1012
因为1012,属于第二条记录fsNums字段所包含的值(1010-1020)；这条语句，要更改第二条语句fsAlradyNums的值为1015,1012

语句类似如下
update T1 set fsAlreadyNums=fsAlreadyNums+","+1012 where fsNums like '%1012%' or .................????????

------解决方案--------------------

SQL code


Create function IsInCardString_T(@CardString varchar(8000),@CardNo varchar(4))
returns int
as
begin
    declare @temp table(a varchar(200))
    declare @i int
    set @CardString = rtrim(ltrim(@CardString))+','
    set @i = charindex(',', @CardString)
    while @i >= 1
    begin
        insert @temp values(left(@CardString, @i - 1))
        set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i)
        set @i = charindex(',', @CardString)
    end
    if exists(select 1
    from (
           select case when charindex('-',a) > 0 then left(a,4) else a end as s,
           case when charindex('-',a) > 0 then right(a,4) else a end as e
           from @temp
          ) a
    where @CardNo between s and e)
        set @i= 1
    else
        set @i= 0
    return @i
end


declare @表T1 table (fsNums varchar(20),fsAlreadyNums varchar(10))
insert into @表T1
select '1001,1002,1003-1008','1001' union all
select '1009,1010-1020','1015'

declare @p varchar(10) set @p=1002

update @表T1 set fsAlreadyNums=fsAlreadyNums+','+@p
where dbo.IsInCardString_T(fsNums,@p)=1

select * from @表T1
/*
fsNums               fsAlreadyNums
-------------------- -------------
1001,1002,1003-1008  1001,1002
1009,1010-1020       1015
*/

declare @表T2 table (fsNums varchar(20),fsAlreadyNums varchar(10))
insert into @表T2
select '1001,1002,1003-1008','1001' union all
select '1009,1010-1020','1015'

declare @q varchar(10) set @q='1012'

update @表T2 set fsAlreadyNums=fsAlreadyNums+','+@q
where dbo.IsInCardString_T(fsNums,@q)=1

select * from @表T2

/*
fsNums               fsAlreadyNums
-------------------- -------------
1001,1002,1003-1008  1001
1009,1010-1020       1015,1012
*/

求一条单表查询的复杂SQL语句？解决办法

相关推荐