如何在现有WooCommerce产品SQL查询中添加产品标签列？

要在你的现有查询中新增产品标签列，需要额外关联product_tag分类法对应的数据库表，并用GROUP_CONCAT聚合函数将单产品的多个标签合并为逗号分隔的字符串（适配单产品多标签的场景）。以下是修改后的完整查询及说明：

修改后的完整SQL查询 #

SELECT 
p.ID,
p.post_type as Art,
p.post_title AS Produkt,
p.post_excerpt AS Beschreibung,
t.name AS Produzent,
-- 新增产品标签列，逗号分隔多个标签
GROUP_CONCAT(DISTINCT t_tags.name SEPARATOR ', ') AS Produkt_Tags,

MAX(CASE WHEN pm1.meta_key = 'faktor' then pm1.meta_value ELSE NULL END) as Faktor,
MAX(CASE WHEN pm1.meta_key = 'produzentenvergleich' then pm1.meta_value ELSE NULL END) as PV,
MAX(CASE WHEN pm1.meta_key = 'einkaufspreis' then pm1.meta_value ELSE NULL END) as EP,
MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as RegPreis,
MAX(CASE WHEN pm1.meta_key = 'variationspreis' then pm1.meta_value ELSE NULL END) as VarPreis,
MAX(CASE WHEN pm1.meta_key = '_purchase_note' then pm1.meta_value ELSE NULL END) as Liefertermin,
MAX(CASE WHEN pm1.meta_key = '_stock_status' AND pm1.meta_value = 'instock' then 'Ja' END) as lieferbar,
MAX(CASE WHEN pm1.meta_key = 'produzenten_bestellvorgabe' then pm1.meta_value ELSE NULL END) as Vorgabe,
MAX(CASE WHEN pm1.meta_key = 'durchschnittsgewicht' then pm1.meta_value ELSE NULL END) as ø Gewicht, 
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_min' then pm1.meta_value ELSE NULL END) as Min_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_max' then pm1.meta_value ELSE NULL END) as Max_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_default' then pm1.meta_value ELSE NULL END) as Def_Menge,
MAX(CASE WHEN pm1.meta_key = '_alg_wc_pq_step' then pm1.meta_value ELSE NULL END) as Step_Menge,
MAX(CASE WHEN pm1.meta_key = '_unit' then pm1.meta_value ELSE NULL END) as Unit,
MAX(CASE WHEN pm1.meta_key = '_sale_price_label' then pm1.meta_value ELSE NULL END) as Preis_Label


FROM KtS4szE8u_posts p 
LEFT JOIN KtS4szE8u_postmeta pm1 ON pm1.post_id = p.ID
-- 原产品分类关联逻辑
LEFT JOIN KtS4szE8u_term_relationships AS tr ON tr.object_id = p.ID
JOIN KtS4szE8u_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN KtS4szE8u_terms AS t ON t.term_id = tt.term_id
-- 新增产品标签关联，用别名避免与原分类关联冲突
LEFT JOIN KtS4szE8u_term_relationships AS tr_tags ON tr_tags.object_id = p.ID
LEFT JOIN KtS4szE8u_term_taxonomy AS tt_tags ON tt_tags.taxonomy = 'product_tag' AND tt_tags.term_taxonomy_id = tr_tags.term_taxonomy_id
LEFT JOIN KtS4szE8u_terms AS t_tags ON t_tags.term_id = tt_tags.term_id

WHERE p.post_type = 'product' AND p.post_status = 'publish' AND substring(p.post_title,1,12) <> 'Mindermengen' and substring(p.post_title,1,9) <> 'Glaspfand' and substring(p.post_title,1,16) <> 'Mitgliedsbeitrag' and substring(p.post_title,1,10) <> 'Barzahlung' and substring(p.post_title,1,1) <> '€' and substring(p.post_title,1,9) <> 'Umbuchung' and  substring(p.post_title,1,7) <> 'Gutschr'
GROUP BY p.ID,p.post_title

修改要点说明 #

1. 新增标签列：使用GROUP_CONCAT(DISTINCT t_tags.name SEPARATOR ', ')实现多标签合并，DISTINCT用于过滤重复标签，保证结果整洁。
2. 标签表关联：通过三个LEFT JOIN关联产品标签对应的系统表，用tr_tags、tt_tags、t_tags作为别名，避免与原产品分类的关联表名冲突。
3. LEFT JOIN选型：确保没有设置标签的产品也能正常出现在查询结果中，不会被过滤。

内容的提问来源于stack exchange，提问作者uebes