UPDATE 问题请教!

[zhangyang555]

有两张表，表1结构和内容如下：

StartValue EndValue IDList
10 25
25 60
60 85
85 105
105 125

表2结构和内容如下：

ID Value
1 12
2 16
3 28
4 88
5 111


现在需要把表2中ID更新到表1中，如果Value在表1中的StartValue和EndValue之间，则将ID更新到表1的IDList中，最终表1的结果如下：
StartValue EndValue IDList
10 25 1,2
25 60 3
60 85
85 105 4
105 125 5

谢谢各位指教。

[云水千寻]

帮顶+学习

[zhangyang555]

补充一下，表1中还有个IDCount字段，在更新IDList的同时需要更新此字段，表示有几个值符合本条记录的取值范围：
StartValue EndValue IDList IDCount
10 25 1,2 2
25 60 3 1
60 85 0
85 105 4 1
105 125 5 1

[pt1314917]

--> 测试数据: 表1
create table  表1(StartValue int,EndValue int,IDList sql_variant)
insert into 表1
select 10,25,null union all
select 25,60,null union all
select 60,85,null union all
select 85,105,null union all
select 105,125,null
--> 测试数据: 表2
create table 表2(ID int,Value int)
insert into 表2
select 1,12 union all
select 2,16 union all
select 3,28 union all
select 4,88 union all
select 5,111


create function Getid(@startvalue int,@endvalue int)
returns varchar(500)
as
begin
	declare @t varchar(500)
	select @t=isnull(@t+',','')+ltrim(id) from 表2	where value between @startvalue and @endvalue
	return @t
end
go

update 表1 set idlist=dbo.Getid(startvalue,endvalue) from 表1
go

select * from 表1

--结果：
StartValue  EndValue    IDList
----------- ----------- --------
10          25          1,2
25          60          3
60          85          NULL
85          105         4
105         125         5

[让你望见影子的墙]

字符串合并。
--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab

go

[pt1314917]

--> 测试数据: 表1
create table  表1(StartValue int,EndValue int,IDList varchar(100),idcount int)
insert into 表1
select 10,25,null,null union all
select 25,60,null,null union all
select 60,85,null,null union all
select 85,105,null,null union all
select 105,125,null,null
--> 测试数据: 表2
create table 表2(ID int,Value int)
insert into 表2
select 1,12 union all
select 2,16 union all
select 3,28 union all
select 4,88 union all
select 5,111
go

create function Getid(@startvalue int,@endvalue int)
returns varchar(500)
as
begin
	declare @t varchar(500)
	select @t=isnull(@t+',','')+ltrim(id) from 表2	where value between @startvalue and @endvalue
	return @t
end
go

update 表1 set idlist=dbo.Getid(startvalue,endvalue),
idcount=(select count(1) from 表2 where value between a.startvalue and a.endvalue)
 from 表1 a
go

select * from 表1

--结果：
StartValue  EndValue    IDList         idcount
----------- ----------- ---------- -----------
10          25          1,2            2
25          60          3              1
60          85          NULL           0
85          105         4              1
105         125         5              1

[拈花一笑万山红]

学习

[zhangyang555]

感谢pt1314917，也感谢其他各位的参与。结帖。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-12-24 21:12:39
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[ta]
if object_id('[ta]') is not null drop table [ta]
go 
create table [ta]([StartValue] int,[EndValue] int,[IDList] varchar(30),[IDCount] int)
insert [ta]
select 10,25,null,null union all
select 25,60,null,null union all
select 60,85,null,null union all
select 85,105,null,null union all
select 105,125,null,null
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[Value] int)
insert [tb]
select 1,12 union all
select 2,16 union all
select 3,28 union all
select 4,88 union all
select 5,111
--------------开始查询--------------------------

update 
 ta 
set 
 IDList=stuff((select ','+ltrim([id]) from tb b where  b.value between a.startvalue and a.endvalue for xml path('')), 1, 1, ''),
 idcount=(select count(1) from tb where value between a.startvalue and a.endvalue)
from
 ta a 


 select * from ta 
----------------结果----------------------------
/* StartValue  EndValue    IDList                         IDCount
----------- ----------- ------------------------------ -----------
10          25          1,2                            2
25          60          3                              1
60          85          NULL                           0
85          105         4                              1
105         125         5                              1

(5 行受影响)
*/