使用SQL将多个字段合并为一个 [英] Concatenate several fields into one with SQL
问题描述
我有三个表tag
,page
,pagetag
下面的数据
页面
page
ID NAME
1 page 1
2 page 2
3 page 3
4 page 4
标签
tag
ID NAME
1 tag 1
2 tag 2
3 tag 3
4 tag 4
页面标签
pagetag
ID PAGEID TAGID
1 2 1
2 2 3
3 3 4
4 1 1
5 1 2
6 1 3
我想在单个查询中获得一个包含SQL每一页的对应标签名的字符串.这是我想要的输出.
I would like to get a string containing the correspondent tag names for each page with SQL in a single query. This is my desired output.
ID NAME TAGS
1 page 1 tag 1, tag 2, tag 3
2 page 2 tag 1, tag 3
3 page 3 tag 4
4 page 4
SQL可能吗?
我正在使用MySQL.但是,如果可能的话,我希望有一个独立于数据库供应商的解决方案.
I am using MySQL. Nonetheless, I would like a database vendor independent solution if possible.
推荐答案
塞尔吉奥·德尔阿莫:
Sergio del Amo:
但是,我没有获得没有标签的页面.我想我需要用左外部联接编写查询.
However, I am not getting the pages without tags. I guess i need to write my query with left outer joins.
SELECT pagetag.id, page.name, group_concat(tag.name)
FROM
(
page LEFT JOIN pagetag ON page.id = pagetag.pageid
)
LEFT JOIN tag ON pagetag.tagid = tag.id
GROUP BY page.id;
这不是一个非常漂亮的查询,但是应该给您您想要的内容-在您上面发布的示例中,第4页的pagetag.id
和group_concat(tag.name)
将是null
,但是该页面将出现在结果中.
Not a very pretty query, but should give you what you want - pagetag.id
and group_concat(tag.name)
will be null
for page 4 in the example you've posted above, but the page shall appear in the results.
这篇关于使用SQL将多个字段合并为一个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!