我们有时候需要在当前分类下自动获取所有文章的所有标签，来进行自动标签筛选。那么要获取当前分类下所有文章的所有标签聚合，可以使用以下的MySQL查询语句：
|
1 |
SELECT DISTINCT wp_terms.term_id, wp_terms.name, wp_terms.slug FROM wp_terms INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id INNER JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID WHERE wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_taxonomy.term_id IN ( SELECT term_id FROM wp_term_taxonomy WHERE taxonomy = 'category' AND term_id = [当前分类ID] ) AND wp_posts.post_status = 'publish' |
其中，[当前分类ID]需要替换为实际的分类ID。
这个查询语句会返回当前分类下所有文章的所有标签的聚合结果，包括每个标签的ID、名称和slug。
如果你想在WordPress中使用这个查询语句，可以使用$wpdb对象来执行查询，示例代码如下：
|
1 |
<span class="hljs-keyword">global</span> <span class="hljs-variable">$wpdb</span>; <span class="hljs-variable">$tags</span> = <span class="hljs-variable">$wpdb</span>-><span class="hljs-title function_ invoke__">get_results</span>(<span class="hljs-string">" SELECT DISTINCT wp_terms.term_id, wp_terms.name, wp_terms.slug FROM <span class="hljs-subst">{$wpdb->terms}</span> AS wp_terms INNER JOIN <span class="hljs-subst">{$wpdb->term_taxonomy}</span> AS wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id INNER JOIN <span class="hljs-subst">{$wpdb->term_relationships}</span> AS wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id INNER JOIN <span class="hljs-subst">{$wpdb->posts}</span> AS wp_posts ON wp_term_relationships.object_id = wp_posts.ID WHERE wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_taxonomy.term_id IN ( SELECT term_id FROM <span class="hljs-subst">{$wpdb->term_taxonomy}</span> WHERE taxonomy = 'category' AND term_id = [当前分类ID] ) AND wp_posts.post_status = 'publish' "</span>); |
这个代码会返回一个包含所有标签聚合结果的数组，每个元素是一个对象，包含term_id、name和slug三个属性。
