WooCommerce产品的SQL查询：获取sku和产品标签

产品变体不处理任何自定义分类，如类别、标签、属性.

产品标签的分类只有product_tag.

在下面的SQL查询中，您将获得产品ID、sku、标记名和ID (replace "wp_" from the table names with the correct table index if needed):

SELECT p.ID AS product_id,
pm.meta_value AS product_sku,
t.name AS tag_name,
t.term_id AS tag_id
FROM wp_posts p 
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = 'product' 
AND p.post_status = 'publish'
AND pm.meta_key = '_sku'
AND tt.taxonomy = 'product_tag'

对于产品变体类型，请使用(where we get the product tag name and ID from the parent variable product):

SELECT p.ID AS product_id,
pm.meta_value AS product_sku,
t.name AS tag_name,
t.term_id AS tag_id
FROM wp_posts p 
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
LEFT JOIN wp_term_relationships tr ON p.post_parent = tr.object_id 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = 'product_variation' 
AND p.post_status = 'publish'
AND pm.meta_key = '_sku'
AND tt.taxonomy = 'product_tag'

注意:只有带有产品标签的产品才会列出.