我有一个自定义的表格,用来跟踪访问一个网站。下表如下:
日期,post_slug,用户
我想要创建一个查询(mysql),在这个表中添加第4列,包含文章的所有类别。
我不想为每个类别创建一个新行,只想为每个类别创建一行,并将所有类别添加到此表的每一行中。
信息应该存储在wp_term_relationship和wp_terms中,但是我不知道如何为每个类别创建一行而不是n行
发布于 2017-02-21 16:11:31
您应该做的主要事情是group by wp_posts.id。此命令将所有相等的id合并到一行。现在,您应该使用group_concat( FIELDNAME )在一列中获取所有所需字段。
您需要类别名称还是类别id?
如果您希望有类别名称:
SELECT tbl.*,GROUP_CONCAT(term.name) FROM
YOURTABLE as tbl
left join wp_posts as p
on p.post_name = tbl.post_slug
left join wp_term_relationships as rels
on rels.object_id = p.ID
left JOIN wp_term_taxonomy as tx
on tx.term_taxonomy_id = rels.term_taxonomy_id
left join wp_terms as term
on term.term_id = tx.term_id
where tx.taxonomy = "category"
group by p.ID如果您需要类别id:
SELECT tbl.*,GROUP_CONCAT(tx.term_taxonomy_id) FROM
YOURTABLE as tbl
left join wp_posts as p
on p.post_name = tbl.post_slug
left join wp_term_relationships as rels
on rels.object_id = p.ID
left JOIN wp_term_taxonomy as tx
on tx.term_taxonomy_id = rels.term_taxonomy_id
-- left join wp_terms as term
-- on term.term_id = tx.term_id
where tx.taxonomy = "category"
group by p.ID注意:
YOURTABLE是您的3列表.where tx.taxonomy = "category"编辑为where tx.taxonomy = "post_tag"即可。and p.post_type ="YOURPOSTTYPE"行之后添加where ..。https://stackoverflow.com/questions/42371657
复制相似问题