问题描述
当前我的查询中有一个GROUP_CONCAT
,它抓住相关标签,并且返回的tags
列值类似于:
Currently my query has a GROUP_CONCAT
which grabs relating tags and the returning tags
column value is something like:
布鲁克林,纽约曼哈顿"
'Brooklyn, Manhattan, New York'
来自
SELECT
post.id,
post.name,
GROUP_CONCAT( tags.name order by tags.name ) AS tags
FROM post
LEFT JOIN tags_items
ON post.id = tags_items.post
LEFT JOIN tags
ON tags_items.tag = tags.id
使用此字符串,在我的服务器端代码中,我用逗号将其分割并循环生成标签.但是,现在我需要进行这些链接,并且我需要知道每个标签的各自ID,以便生成诸如<a href="?tag=1">New York</a>
的链接.我的HTML结构如下:
With this string, in my server-side code I'm splitting it up by the comma and looping through to generate tags. However, now I need to make these links and I need to know the respective id of each of the tags in order to generate a link such as <a href="?tag=1">New York</a>
. My HTML structure will be as such:
<div class=post>
<h2>Rakim Allah!</h2>
<div class=tags>
<a href="/tags/1">Brooklyn</a>
<a href="/tags/2">Manhattan</a>
<a href="/tags/3">New York</a>
</div>
</div>
tags
表:
id name
1 Brooklyn
2 Manhattan
tags_items
表(无pk):
post tag
1 1
推荐答案
您可以尝试将ID添加到带有不同定界符的组列表中的每个项目:
You could try adding the ID to each item in the group list w/ a different delimiter:
SELECT post.id, post.name,
GROUP_CONCAT( CONCAT(tags.id, ';', tags.name) order by tags.name ) AS tags
FROM post
LEFT JOIN tags
ON post.id = tags.post_id;
现在,您只需对分号进行额外的拆分即可获取ID和名称.
Now you'd just have to do an additional split on semi-colons to get the id and name.
这篇关于SQL:重构GROUP_CONCAT查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!