如何在WooCommerce订单SQL查询中关联税率名称并组合显示

要实现将税率名称（如GST5%）与税值（如12.5）拼接显示，需要通过订单项目元数据表关联到税率表，具体修改后的SQL如下：

SELECT
    p.ID AS order_id,
    DATE(p.post_date) AS order_created_on,
    cpm.meta_value AS currency,
    CONCAT(MAX(CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END), " ", MAX(CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END)) AS 'Customer Name',
    (SELECT GROUP_CONCAT(order_item_name SEPARATOR '|') FROM wp_filn_woocommerce_order_items WHERE order_id = p.ID) AS order_items,
    MAX(CASE WHEN pm.meta_key = '_tax_class' AND p.ID = pm.post_id THEN pm.meta_value END) AS Tax_class,
    -- 拼接税率名称与对应税值
    GROUP_CONCAT(DISTINCT CONCAT(tr.tax_rate_name, ' ', oim.meta_value) SEPARATOR ' | ') AS tax_with_name,
    MAX(CASE WHEN pm.meta_key = '_order_tax' AND p.ID = pm.post_id THEN pm.meta_value END) AS total_order_tax,
    DATE(MAX(CASE WHEN pm.meta_key = '_paid_date' AND p.ID = pm.post_id THEN pm.meta_value END)) AS paid_on,
    MAX(CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END) AS order_total
FROM
    wp_filn_posts p
    JOIN wp_filn_postmeta cpm ON p.ID = cpm.post_id
    JOIN wp_filn_postmeta pm ON p.ID = pm.post_id
    JOIN wp_filn_woocommerce_order_items oi ON p.ID = oi.order_id
    -- 关联订单项目元数据，获取税费详情和税率ID
    LEFT JOIN wp_filn_woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
        AND oim.meta_key IN ('tax_amount', 'tax_rate_id')
    -- 通过税率ID关联税率表，获取税率名称
    LEFT JOIN wp_filn_woocommerce_tax_rates tr ON oim.meta_value = tr.tax_rate_id
WHERE
    p.post_type = 'shop_order'
    AND p.post_date BETWEEN DATE '2022-01-01' AND '2022-06-30'
    AND cpm.meta_key = '_order_currency'
    AND p.post_status = 'wc-completed'
    AND cpm.meta_value = 'CAD'
GROUP BY
    p.ID

关键修改说明： #

• 新增LEFT JOIN wp_filn_woocommerce_order_itemmeta：从订单项目元数据中提取每个税费项的金额（tax_amount）和对应的税率ID（tax_rate_id）。
• 新增LEFT JOIN wp_filn_woocommerce_tax_rates：通过税率ID关联到税率表，获取tax_rate_name（如GST5%）。
• 新增tax_with_name字段：用GROUP_CONCAT和CONCAT将税率名称与对应税值拼接，若订单存在多个税率（如联邦税+省税），会用|分隔显示。
• 保留原total_order_tax字段：显示订单总税值，方便对比。

如果你的订单每个税率对应单独的税项，这个查询会准确拼接每个税率名称和对应的税额；若需要只显示总税值加税率名称，可调整GROUP_CONCAT逻辑，比如只取唯一的税率名称加总税值。

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