34,576
社区成员
发帖
与我相关
我的任务
分享
--文章列表
create table Article
(
ArticleId int,
KeyWords varchar(100)
)
insert into Article
select 1,'联合卡车,卡车' union all
select 2,'联合卡车,货车' union all
select 3,'东风,卡车,油车' union all
select 4,'联合卡车,搅拌车,自卸,轻卡'
--关键词关联文章数量的表
create table KeyWords_ArticleCount
(
KeyWords varchar(100),
ArticleCount int
)
--关键词文章关联表
create table Article_KeyWords_Relation
(
KeyWords varchar(100),
ArticleId int
)
insert Article_KeyWords_Relation
select b.KeyWords,a.ArticleId
from (select ArticleId,KeyWords=case when isnull(KeyWords,'') = '' then '其他' else KeyWords end from Article) a
cross apply (
select KeyWords=SUBSTRING(a.KeyWords,number,CHARINDEX(',',a.KeyWords+',',number+1)-number)
from master..spt_values
where CHARINDEX(',',','+a.KeyWords,number) = number AND type = 'P'
) b
insert KeyWords_ArticleCount
select KeyWords,count(1)
from Article_KeyWords_Relation group by KeyWords
insert Article_KeyWords_Relation
select b.KeyWords,a.ArticleId
from (select ArticleId,KeyWords=case when isnull(KeyWords,'') = '' then '其他' else KeyWords end from Article) a
cross apply (
select KeyWords=SUBSTRING(a.KeyWords,number,CHARINDEX(',',a.KeyWords+',',number+1)-number)
from master..spt_values
where CHARINDEX(',',','+a.KeyWords,number) = number AND type = 'P'
) b
--insert KeyWords_ArticleCount 楼主,这里ArticleCount的类型不对,int类的无法保存 1(3) 这样的数据
select KeyWords,ltrim(count(1))+' ('+stuff((select ','+ltrim(ArticleId) from Article_KeyWords_Relation where KeyWords = a.KeyWords for xml path('')),1,1,'')+')'
from Article_KeyWords_Relation a group by KeyWords
insert into KeyWords_ArticleCount
select KeyWords,count(KeyWords)
from Article_KeyWords_Relation t
group by KeyWords
KeyWords ArticleCount
-------------------- ------------
东风 1
货车 1
搅拌车 1
卡车 2
联合卡车 3
轻卡 1
油车 1
自卸 1
(8 行受影响)
drop table Article,KeyWords_ArticleCount,Article_KeyWords_Relation
--文章列表
create table Article
(
ArticleId int,
KeyWords varchar(100)
)
insert into Article
select 1,'联合卡车,卡车' union all
select 2,'联合卡车,货车' union all
select 3,'东风,卡车,油车' union all
select 4,'联合卡车,搅拌车,自卸,轻卡'
--关键词关联文章数量的表
create table KeyWords_ArticleCount
(
KeyWords varchar(20),
ArticleCount int
)
--关键词文章关联表
create table Article_KeyWords_Relation
(
KeyWords varchar(20),
ArticleId int
)
insert into Article_KeyWords_Relation
SELECT B.KeyWords,A.ArticleId
FROM(
SELECT ArticleId, KeyWords = CONVERT(xml,'<root><v>' + REPLACE(KeyWords, ',', '</v><v>') + '</v></root>') FROM Article
)A
OUTER APPLY(
SELECT KeyWords = N.v.value('.', 'varchar(100)') FROM A.KeyWords.nodes('/root/v') N(v)
)B
ORDER BY B.KeyWords,A.ArticleId
insert into KeyWords_ArticleCount
select KeyWords,count(KeyWords)
from Article_KeyWords_Relation t
group by KeyWords,ArticleId
select * from Article_KeyWords_Relation
select * from KeyWords_ArticleCount
drop table Article,KeyWords_ArticleCount,Article_KeyWords_Relation
KeyWords ArticleId
-------------------- -----------
东风 3
货车 2
搅拌车 4
卡车 1
卡车 3
联合卡车 1
联合卡车 2
联合卡车 4
轻卡 4
油车 3
自卸 4
(11 行受影响)
KeyWords ArticleCount
-------------------- ------------
卡车 1
联合卡车 1
货车 1
联合卡车 1
东风 1
卡车 1
油车 1
搅拌车 1
联合卡车 1
轻卡 1
自卸 1
insert Article_KeyWords_Relation
select b.KeyWords,a.ArticleId
from Article a
cross apply (
select KeyWords=SUBSTRING(a.KeyWords,number,CHARINDEX(',',a.KeyWords+',',number+1)-number)
from master..spt_values
where CHARINDEX(',',','+a.KeyWords,number) = number AND type = 'P'
) b
select * from Article_KeyWords_Relation
/*
KeyWords ArticleId
---------------------------------------------------------------------------------------------------- -----------
联合卡车 1
卡车 1
联合卡车 2
货车 2
东风 3
卡车 3
油车 3
联合卡车 4
搅拌车 4
自卸 4
轻卡 4
(11 行受影响)
*/
--insert KeyWords_ArticleCount 楼主,这里ArticleCount的类型不对,int类的无法保存 1(3) 这样的数据
select KeyWords,ltrim(count(1))+' ('+stuff((select ','+ltrim(ArticleId) from Article_KeyWords_Relation where KeyWords = a.KeyWords for xml path('')),1,1,'')+')'
from Article_KeyWords_Relation a group by KeyWords
/*
KeyWords
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
东风 1 (3)
货车 1 (2)
搅拌车 1 (4)
卡车 2 (1,3)
联合卡车 3 (1,2,4)
轻卡 1 (4)
油车 1 (3)
自卸 1 (4)
(8 行受影响)
*/
select ka.KeyWords
,stuff((select ','+ltrim(ArticleId) from Article a where CHARINDEX(','+ka.KeyWords+',',','+a.KeyWords+',')>0 for xml path('')),1,1,'') as ArticleCount
into #t
from KeyWords_ArticleCount ka
select * from #t
select a.KeyWords
,substring(ArticleCount, b.number , charindex(',' , ArticleCount + ',' , b.number) - b.number)
from #t a join master..spt_values b on b.type='p' and b.number between 1 and LEN(ArticleCount)
where SUBSTRING(','+A.ArticleCount,number,1)=','
------------------
KeyWords (无列名)
东风 3
货车 2
搅拌车 4
卡车 1
卡车 3
联合卡车 1
联合卡车 2
联合卡车 4
轻卡 4
油车 3
自卸 4
--文章列表
create table Article
(
ArticleId int,
KeyWords varchar(100)
)
insert into Article
select 1,'联合卡车,卡车' union all
select 2,'联合卡车,货车' union all
select 3,'东风,卡车,油车' union all
select 4,'联合卡车,搅拌车,自卸,轻卡'
--关键词关联文章数量的表
create table KeyWords_ArticleCount
(
KeyWords varchar(100),
ArticleCount int
)
declare @s nvarchar(max)
select @s=ISNULL(@s+',','')+KeyWords from Article
set @s=REPLACE(@s,',',''' union select ''')
insert into KeyWords_ArticleCount(KeyWords)
exec('select '''+@s+'''')
select ka.KeyWords
,(select cast(COUNT(*) as varchar(10)) from Article a where CHARINDEX(','+ka.KeyWords+',',','+a.KeyWords+',')>0)+'('+stuff((select ','+ltrim(ArticleId) from Article a where CHARINDEX(','+ka.KeyWords+',',','+a.KeyWords+',')>0 for xml path('')),1,1,'')+')' as ArticleCount
from KeyWords_ArticleCount ka
-------------------
KeyWords ArticleCount
东风 1(3)
货车 1(2)
搅拌车 1(4)
卡车 2(1,3)
联合卡车 3(1,2,4)
轻卡 1(4)
油车 1(3)
自卸 1(4)