需要将一个查询的结果集存放到另外一个表的字段中
需求如下：
有2个表：表1、表2
表1结构：
Id Title
1 A
2 B
3 C
4 D
5 E
表2结构：
Id Title Table1Id
1 ABC 0
2 AB 0
3 CD 0
现在需要做的是
如果表1中Title字段中的值有包含在表2字段的Title中的，那么将表1中的Id号存放到表2的Table1Id中，并通过分号连接各个Id号。
需要实现的结果为：
表2：
Id Title Table1Id
1 ABC 1;2;3
2 AB 1;2
3 CD 3;4
尝试用
update [表2] set Table1Id = (Select Id from [表1] where Title Like '%' + [表2].Title+ '%')
会出现错误提示
“子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。”
请高手指导一下，可以用哪些方法实现？
------解决方案--------------------
- SQL code
--写个函数处理吧，想不到什么方法set nocount on;if object_id('t1') is not null drop table t1;create table t1(id int,title varchar(5));if object_id('t2') is not null drop table t2;create table t2(id int,title varchar(100),tableid varchar(100));insert into t1 select 1,'A' union select 2,'B' union select 3,'C' union select 4,'D' union select 5,'E';insert into t2 select 1,'ABC','' union select 2,'AB','' union select 3,'CD',''if object_id('t_f') is not null drop function t_f;gocreate function dbo.t_f(@title varchar(100))returns varchar(100)asbegin declare @count int select @count=1 declare @retval varchar(100); while @count<=len(@title) begin set @retval=isnull(@retval,'')+(select convert(varchar(10),id)+';' from t1 where title=substring(@title,@count,1)) select @[email protected]+1; end return @retvalendgoupdate t2 set tableid=dbo.t_f(title);select * from t2;/*id title tableid----------- ---------- ----------1 ABC 1;2;3;2 AB 1;2;3 CD 3;4;*/
------解决方案--------------------
- SQL code
--CREATE TABLE t1 --(Id INT,Title VARCHAR(2)) --INSERT INTO t1 --SELECT 1, 'A' --UNION ALL --SELECT 2, 'B' --UNION ALL --SELECT 3, 'C' --UNION ALL --SELECT 4, 'D' --UNION ALL --SELECT 5, 'E' --CREATE TABLE t2 --(Id INT,Title VARCHAR(10), Table1Id INT ) --INSERT INTO t2 --SELECT 1, 'ABC', 0 --UNION ALL --SELECT 2, 'AB', 0 --UNION ALL --SELECT 3, 'CD', 0 --SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid --FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%' ;with cte as(SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%') ,cte1 as (select id, bid,Title,table1id,cast(aid as nvarchar(100))aid from cte where id=1 union all select a.id, a.bid,a.Title,a.table1id,cast(b.aid+';'+a.aid as nvarchar(100)) from cte a join cte1 b on a.bid=b.bid and a.id=b.id+1) --SELECT * FROM cte1 select bid,Title,table1id,aid INTO #t from cte1 a where id=(select max(id) from cte where bid=a.bid) order by bid option (MAXRECURSION 0) /* bid Title table1id aid ----------- ---------- ----------- ---------------------------------------------------------------- 1 ABC 0 1;2;3 2 AB 0 1;2 3 CD 0 3;4 (3 行受影响) */ UPDATE a SET a.table1id=b.table1id FROM t2 a INNER JOIN #t b ON a.id=b.bid
------解决方案--------------------
- SQL code
DECLARE @t1 TABLE(ID VARCHAR(50),Title VARCHAR(10));if object_id('tempdb..#t') is not null drop table #tDECLARE @t2 TABLE(ID VARCHAR(50),Title VARCHAR(10),TableId VARCHAR(10));INSERT INTO @t1 SELECT '1','A' UNION SELECT '2','B' UNION SELECT '3','C' UNION SELECT '4','D' UNION SELECT '5','E'INSERT INTO @t2SELECT '1','ABC','0'UNIONSELECT '2','AB','0'UNIONSELECT '3','CD','0'----准备数据完毕。。。。开始查询;WITH c1 AS( SELECT t2.Title,t1.ID FROM @t2 t2 JOIN @t1 t1 ON CHARINDEX(t1.Title, t2.Title, 0)<>0 )SELECT c1.Title 'Title',STUFF((SELECT ';'+LTRIM(ID) FROM c1 tempC WHERE title=c1.title FOR XML PATH('')),1,1,'') AS 'UnionRow'INTO #tFROM c1GROUP BY c1.Title UPDATE t2 --查询出来的结果集更新到临时表#t2SET t2.TableId = t.UnionRowFROM #t t JOIN @t2 t2 on t.Title=t2.TitleSELECT * FROM @t2--应该换成表变量来处理的