22,209
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [class]
IF OBJECT_ID('[class]') IS NOT NULL
DROP TABLE [class]
GO
CREATE TABLE [class] ([id] [int],[class] [nvarchar](10))
INSERT INTO [class]
SELECT '1','娱乐新闻' UNION ALL
SELECT '2','行业新闻' UNION ALL
SELECT '3','政治新闻' UNION ALL
SELECT '4','八卦新闻'
--> 生成测试数据表: [news]
IF OBJECT_ID('[news]') IS NOT NULL
DROP TABLE [news]
GO
CREATE TABLE [news] ([id] [int],[newsname] [nvarchar](10),[newsclass] [nvarchar](10))
INSERT INTO [news]
SELECT '1','新闻标题','1,3,4' UNION ALL
SELECT '2','新闻标题','1,2,4'
--SELECT * FROM [class]
--SELECT * FROM [news]
-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@s varchar(1000)
)RETURNS varchar(1000)
AS
BEGIN
SELECT @S=REPLACE(','+@S+',',','+LTRIM([id])+',',','+[class]+','),
@S=REPLACE(@S,',,',',')
FROM [class]
RETURN LEFT(STUFF(@S,1,1,''),LEN(@S)-2)
END
GO
SELECT [id],[newsname],[newsclass]=dbo.[fn_test]([newsclass]) FROM [news]
/*
id newsname newsclass
----------- ---------- ----------------------------------------------------
1 新闻标题 娱乐新闻,政治新闻,八卦新闻
2 新闻标题 娱乐新闻,行业新闻,八卦新闻
(2 行受影响)
*/
--> 测试数据:[class]
if object_id('[class]') is not null drop table [class]
go
create table [class]([id] int,[class] varchar(8))
insert [class]
select 1,'娱乐新闻' union all
select 2,'行业新闻' union all
select 3,'政治新闻' union all
select 4,'八卦新闻'
--> 测试数据:[news]
if object_id('[news]') is not null drop table [news]
go
create table [news]([id] int,[newsname] varchar(8),[newsclass] varchar(5))
insert [news]
select 1,'新闻标题','1,3,4' union all
select 2,'新闻标题','1,2,4'
--------------------------------查询开始------------------------------
if object_id('f_str') is not null drop function f_str
go
create function f_str (@newsclass varchar(200))
returns varchar(200)
as
begin
declare @s varchar(200)
select @s=isnull(@s+',','')+[class] from [class] where charindex(','+cast(id as varchar)+',',','+@newsclass+',')>0
return @s
end
go
select [id],[newsname],[newsclass]= dbo.f_str([newsclass]) from [news]
/*
id newsname newsclass
----------- -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 新闻标题 娱乐新闻,政治新闻,八卦新闻
2 新闻标题 娱乐新闻,行业新闻,八卦新闻
(2 行受影响)
*/