文章表
article
id name
1 奥巴马踢足球标签表
tag
id name
1 体育
2 新闻
3 娱乐关联表
article_tag_rel
article_id tag_id
1 1
1 2
1 3设想的结果
id name tag_ids tag_names
1 奥巴马踢足球 1,2,3 体育,新闻,娱乐请教各位大神 这条查询的sql语句该怎么写
article
id name
1 奥巴马踢足球标签表
tag
id name
1 体育
2 新闻
3 娱乐关联表
article_tag_rel
article_id tag_id
1 1
1 2
1 3设想的结果
id name tag_ids tag_names
1 奥巴马踢足球 1,2,3 体育,新闻,娱乐请教各位大神 这条查询的sql语句该怎么写
with article(id,name)as(
select 1,'奥巴马踢足球'),
tag(id,name)as(
select 1,'体育' union all
select 2,'新闻' union all
select 3,'娱乐'),
article_tag_rel(article_id,tag_id)as(
select 1,1 union all
select 1,2 union all
select 1,3)
select name,stuff((select ','+rtrim(tag.id) from article_tag_rel atr join tag on atr.tag_id=tag.id where atr.article_id=article.id for xml path('')),1,1,'') as tag_ids,stuff((select ','+tag.name from article_tag_rel atr join tag on atr.tag_id=tag.id where atr.article_id=article.id for xml path('')),1,1,'') as tag_names from article
if not object_id(N'Tempdb..#article') is null
drop table #article
Go
Create table #article([id] int,[name] nvarchar(26))
Insert #article
select 1,N'奥巴马踢足球'
GO
if not object_id(N'Tempdb..#tag') is null
drop table #tag
Go
Create table #tag([id] int,[name] nvarchar(22))
Insert #tag
select 1,N'体育' union all
select 2,N'新闻' union all
select 3,N'娱乐'
GO
if not object_id(N'Tempdb..#article_tag_rel') is null
drop table #article_tag_rel
Go
Create table #article_tag_rel([article_id] int,[tag_id] int)
Insert #article_tag_rel
select 1,1 union all
select 1,2 union all
select 1,3
Go
--测试数据结束
SELECT aa.id ,
aa.name ,
STUFF(( SELECT ',' + RTRIM(c.id)
FROM #article a
JOIN #article_tag_rel b ON a.id = b.article_id
JOIN #tag c ON b.tag_id = c.id
WHERE a.id = aa.id
AND a.name = aa.name
FOR
XML PATH('')
), 1, 1, '') AS tag_ids,
STUFF(( SELECT ',' + c.name
FROM #article a
JOIN #article_tag_rel b ON a.id = b.article_id
JOIN #tag c ON b.tag_id = c.id
WHERE a.id = aa.id
AND a.name = aa.name
FOR
XML PATH('')
), 1, 1, '') AS tag_names
FROM #article aa
GROUP BY aa.id,aa.name