34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t1 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10)
);
if object_id('tempdb..#t') is not null
drop table #t
DECLARE @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 @t2
SELECT '1','ABC','0'
UNION
SELECT '2','AB','0'
UNION
SELECT '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 #t
FROM c1
GROUP BY c1.Title
UPDATE t2 --查询出来的结果集更新到临时表#t2
SET t2.TableId = t.UnionRow
FROM #t t JOIN @t2 t2 on t.Title=t2.Title
SELECT * FROM @t2
--应该换成表变量来处理的
DECLARE @t1 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10)
);
if object_id('tempdb..#t') is not null
drop table #t
if object_id('tempdb..#t2') is not null
drop table #t2
CREATE TABLE #t2
(
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 #t2
SELECT '1','ABC','0'
UNION
SELECT '2','AB','0'
UNION
SELECT '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 #t
FROM c1
GROUP BY c1.Title
UPDATE t2 --查询出来的结果集更新到临时表#t2
SET t2.TableId = t.UnionRow
FROM #t t JOIN #t2 t2 on t.Title=t2.Title
SELECT * FROM #t2
--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
--写个函数处理吧,想不到什么方法
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;
go
create function dbo.t_f(@title varchar(100))
returns varchar(100)
as
begin
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 @count=@count+1;
end
return @retval
end
go
update 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;
*/