如何将WordPress关联查询结果按品牌分组统计ID数量与总价？

原始查询及结果 #

原始SQL查询：

SELECT id, meta_key, meta_value, 
FROM  wp_posts post
INNER JOIN wp_postmeta meta 
ON post.ID = meta.POST_ID 
WHERE post.post_status = 'publish' 
AND ( meta.meta_key = 'brand' OR meta.meta_key = 'price' )

查询结果：

id --|-- meta_key --|--  meta_value
-----------------------------------
42 --|-- brand    --|--  mercedes
44 --|-- brand    --|--  honda
46 --|-- brand    --|--  honda
42 --|-- price    --|--  20
44 --|-- price    --|--  20
46 --|-- price    --|--  50

期望输出格式 #

希望得到按品牌分组，统计对应唯一ID数量及总价的结果：

brand --|-- UNIQUE ID COUNT  --|-- Total price sum of IDs relating to brand
----------------------------------------------
honda --|-------  2  ----------|-- 70
mercedes|-------  1  ----------|-- 20

尝试的查询及结果 #

尝试通过CASE语句拆分品牌和价格字段，但未实现分组统计：

SELECT id, meta_key, meta_value,
(CASE WHEN meta_key = 'brand' THEN meta_value END) as brand,
(CASE WHEN meta_key = 'price' THEN meta_value END) as price
FROM  wp_posts post
INNER JOIN wp_postmeta meta 
ON post.ID = meta.POST_ID 
WHERE post.post_status = 'publish' 
AND ( meta.meta_key = 'brand' OR meta.meta_key = 'price' )

得到的结果：

id --|-- meta_key --|--  meta_value  --|-- brand  --|--  Price 
--------------------------------------------------------------
42 --|-- brand    --|--  mercedes    --|-- mercedes |--  NULL
44 --|-- brand    --|--  honda       --|-- honda  --|--  NULL
46 --|-- brand    --|--  honda       --|-- honda  --|--  NULL
42 --|-- price    --|--  20          --|-- NULL   --|--  20
44 --|-- price    --|--  20          --|-- NULL   --|--  20
46 --|-- price    --|--  50          --|-- NULL   --|--  50

解决方案 #

方法1：自连接（直观易懂） #

通过将品牌元数据和价格元数据直接关联，确保每个商品的品牌与价格在同一行，再按品牌分组统计：

SELECT 
    brand_meta.meta_value AS brand,
    COUNT(DISTINCT post.ID) AS `UNIQUE ID COUNT`,
    SUM(CAST(price_meta.meta_value AS UNSIGNED)) AS `Total price sum of IDs relating to brand`
FROM wp_posts post
INNER JOIN wp_postmeta brand_meta 
    ON post.ID = brand_meta.POST_ID 
    AND brand_meta.meta_key = 'brand'
INNER JOIN wp_postmeta price_meta 
    ON post.ID = price_meta.POST_ID 
    AND price_meta.meta_key = 'price'
WHERE post.post_status = 'publish'
GROUP BY brand_meta.meta_value
ORDER BY brand;

方法2：条件聚合+嵌套分组 #

先将同一商品的品牌和价格合并到一行，再按品牌分组统计：

SELECT 
    brand,
    COUNT(DISTINCT post_id) AS `UNIQUE ID COUNT`,
    SUM(price) AS `Total price sum of IDs relating to brand`
FROM (
    SELECT 
        post.ID AS post_id,
        MAX(CASE WHEN meta.meta_key = 'brand' THEN meta.meta_value END) AS brand,
        SUM(CAST(meta.meta_value AS UNSIGNED)) AS price
    FROM wp_posts post
    INNER JOIN wp_postmeta meta 
        ON post.ID = meta.POST_ID 
        AND meta.meta_key IN ('brand', 'price')
    WHERE post.post_status = 'publish'
    GROUP BY post.ID
    HAVING brand IS NOT NULL
) AS post_data
GROUP BY brand
ORDER BY brand;

说明：

• 两种方法都需要将meta_value的价格转换为数值类型（CAST(meta_value AS UNSIGNED)），因为WordPress的wp_postmeta中meta_value是字符串类型，直接求和会出错。
• 自连接方法更适合确定每个商品都同时存在品牌和价格元数据的场景；嵌套分组方法则可以兼容部分商品缺少价格的情况（此时价格会被统计为0）。

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