CREATE TABLE [dbo].[关联表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[标签id] [int] NOT NULL ,
[文章id] [int] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[文章表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[标题] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[内容] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[标签表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GO现在碰到一个问题,如果一个文章给他选了多个标签的话,存储过程怎么写?
(越简单的越好)
[id] [int] IDENTITY (1, 1) NOT NULL ,
[标签id] [int] NOT NULL ,
[文章id] [int] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[文章表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[标题] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[内容] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[标签表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GO现在碰到一个问题,如果一个文章给他选了多个标签的话,存储过程怎么写?
(越简单的越好)
Create proc Insert_关联表
@标签id int,
@文章id int
as
insert into 关联表
select @标签id,@文章id然后程序中循环选的标签。
就可以选多过了
alter proc Insert_关联表
@标签id nvarchar(4000),
@文章id int
as
declare @Str varchar(8000)
set @str ='insert into 关联表 select ID,'+cast(@文章id as nvarchar(10))+' from 标签表 where id in('+@标签id+')'
exec(@str)
Create proc Insert_关联表
@标签id nvarchar(4000),
@文章id int
as
declare @Str varchar(8000)
set @str ='insert into 关联表 select ID,
'+cast(@文章id as nvarchar(10))+' from
标签表 where id in('+@标签id+')'
exec(@str)
----执行SQL
Insert_关联表 '1,2,3,4,5,6',1
上面语句拼出来的语句就是:
insert into 关联表
select ID,1 from 标签表 where id in(1,2,3,4,5,6)
create proc lk
as
begin
select a.标题,c.名称
from 文章表 a join 关联表 b on a.ID=b.文章ID
join 标签表 c on b.标签ID=c.id
end
想要达到的效果是这样比如说前台输入
标题=CSDN论坛是个好论坛
内容=CSDN论坛里面的网友都是高手
标签=1,2,3,4,5现在输入到文章表没有什么问题,就是输入到关联表的时候比较麻烦。
请问有什么好的方法
参考
if object_id('tb') is not null
drop table tb
go
create table tb([text] varchar(10),AA varchar(10),ID int)go
declare @s varchar(8000)
set @s='1,3,6,10,11,17'
insert tb
select
'a',
'b',
id=substring(@s, number, charindex(',', @s + ',', number) - number)
from master..spt_values
where type='p'
and substring(',' + @s,number,1) = ',' select * from tb/*
text AA ID
---------- ---------- -----------
a b 1
a b 3
a b 6
a b 10
a b 11
a b 17(6 行受影响)*/
--你的标题CSDN论坛是个好论坛输入文章表后对应的ID是1 然后这么做
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
insert into 关联表 (标签ID,文章ID)
select *,文章ID=1 from dbo.f_splitSTR(标签,',')
select * from 文章表 a
left join 关联表 b on a.id=b.文章id
left join 标签表 c on c.id=b.标签id
where a.标题='CSDN论坛是个好论坛' and a.内容='CSDN论坛里面的网友都是高手'
and charindex(','+ltrim(c.id)+',',','+'1,2,3,4,5'+',')>0
CREATE TABLE [dbo].[关联表] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[标签id] [int] NOT NULL ,
[文章id] [int] NOT NULL
) ON [PRIMARY]
GO中的字段: [标签id] [int] NOT NULL ,
改为:
[标签ID] varchar(max)
然后存入,类似这样的数据:
1,3,4,6,7