请教一下该如何实现??
例A表
title no text
a 01 阿
a 02 不
a 03 从
B表
title text
a 01/阿*02/不*03/从
...全文
28113打赏收藏
求指定的条件下取两个字段合并为一条记录的方法
A表有title,no,text 三个字段,title为主键 B表为title,text 两个字段,title为主键 现需要将A表的no和text字段合并成一条记录存储到B表 请教一下该如何实现?? 例A表 title no text a 01 阿 a 02 不 a 03 从 B表 title text a 01/阿*02/不*03/从
create table A(title varchar(10),[no] varchar(10),[text] Nvarchar(10))
insert into A select 'a','01',N'阿'
union all select 'a','02',N'不'
union all select 'a','03',N'从'
union all select 'b','01',N'阿'
union all select 'b','02',N'从'
create table B(title varchar(10),[text] Nvarchar(1000))
go
create function dbo.fc_str(@title Nvarchar(100))
returns Nvarchar(100)
as
begin
declare @sql Nvarchar(1000)
set @sql=''
select @sql=@sql+'/'+[no]+[text]+'*' from A where title=@title
return stuff(@sql,1,1,'')
end
go
Insert B Select title,dbo.fc_str(title) From A Group By title
Select * From B
GO
Drop table A,B
Drop function dbo.fc_str
Go
--Result
/*
title text
a 01阿*/02不*/03从*
b 01阿*/02从*
*/
--函数
CREATE function dbo.fc_str(@titles char(10)),@
returns char(255)
as
begin
declare @sql char(255)
set @sql=''
select @sql=@sql+cast([no] as char(10))+'|'+cast([text] as char(100))+'$$$' from A where title=@titles
return stuff(@sql,1,1,'')
end
--存储过程
CREATE PROCEDURE filmurl1 AS
declare @titles char(10),@text char(100),@num int
DECLARE nos CURSOR FOR
select distinct titles from A order by title
OPEN titles
--set QUOTED_IDENTIFIER off
FETCH NEXT FROM titles INTO @titles
WHILE (@@FETCH_STATUS = 0)
begin
set @num =(select count(*) from A where title=@titles)
/*if @num =1
insert into B(title,text)
select title,no+'|'+text
from A
where title=@titles */
if @num >1
insert into B(title,text)
select title,dbo.fc_str(@titles) from A where title=@titles
FETCH NEXT FROM titles INTO @titles
end
DEALLOCATE titles
GO
DECLARE @t TABLE(title VARCHAR(10), no VARCHAR(10), [text] varchar(100))
INSERT @t SELECT 'a', '01','阿'
UNION ALL SELECT 'a', '02','不'
UNION ALL SELECT 'a', '03','从'
-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
title
FROM @t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT T FROM
(
SELECT no+'/'+[text] AS T FROM @t TMP
WHERE title = A.title
) TMP
FOR XML AUTO
), '<TMP T="', '*'), '"/>', ''), 1, 1, '')
)TMP
--结果
/*
title values
---------- -----------
a 01/阿*02/不*03/从
create table tb(title varchar(10),[no] varchar(10),[text] varchar(10))
insert into tb select 'a','01','阿'
union all select 'a','02','不'
union all select 'a','03','从'
go
create function dbo.fc_str(@title varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+'/'+cast([no] as varchar(100))+cast([text] as varchar(100))+'*' from tb where title=@title
return stuff(@sql,1,1,'')
end
go
select title,dbo.fc_str(title) as [text] into B from tb group by title