WordPressのデータベース・テーブル構造【まるわかり】

select
 wp_posts.id
 ,wp_posts.date
 ,wp_posts.post_title
 ,wp_users.display_name as 'writer'
 ,wp_category.name as 'category'
 ,wp_sub_category.name as 'sub_category'
 ,COALESCE(wp_tag.name, '') as 'tag'
from
(
 select
  id
 ,DATE_FORMAT(post_date,'%Y/%m/%d') as date
 ,post_title
 ,post_author
 from
  wp_posts
 where
  post_type='post'
 and
  post_status='publish'
) as wp_posts
left join
 wp_users
 on wp_posts.post_author = wp_users.id
left join
(
 select
  wp_term_relationships.object_id
  ,wp_terms.name
 from
  wp_term_relationships
 left join
  wp_terms
  on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
 left join
  wp_term_taxonomy
  on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
 where
  wp_term_taxonomy.taxonomy = 'category'
  and wp_term_taxonomy.parent = 0
) as wp_category
 on wp_posts.id = wp_category.object_id
left join
(
 select
  wp_term_relationships.object_id
  ,wp_terms.name
 from
  wp_term_relationships
 left join
  wp_terms
  on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
 left join
  wp_term_taxonomy
  on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
 where
  wp_term_taxonomy.taxonomy = 'category'
  and wp_term_taxonomy.parent != 0
) as wp_sub_category
 on wp_posts.id = wp_sub_category.object_id
left join
(
 select
  wp_term_relationships.object_id
  ,GROUP_CONCAT(wp_terms.name SEPARATOR'|') as name
 from
  wp_term_relationships
 left join
  wp_terms
  on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
 left join
  wp_term_taxonomy
  on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
 where
  wp_term_taxonomy.taxonomy = 'post_tag'
 group by
  wp_term_relationships.object_id
) as wp_tag
 on wp_posts.id = wp_tag.object_id
;