WordPress MySQL查询：按品牌统计车辆数及售价总和（含状态筛选）

要实现你的需求，需要关联WordPress的wp_686591_posts表获取文章状态，同时通过自连接wp_686591_postmeta表关联车辆品牌和售价数据。以下是两种场景的SQL查询：

1. 统计publish和pending状态下各品牌的车辆数与总售价 #

这个查询会汇总所有状态为publish或pending的车辆，按品牌分组统计数量和总售价：

SELECT
    brand.meta_value AS brand,
    COUNT(DISTINCT brand.post_id) AS 'number of vehicles',
    SUM(CAST(price.meta_value AS DECIMAL(10,2))) AS Total
FROM wp_686591_postmeta brand
JOIN wp_686591_postmeta price 
    ON brand.post_id = price.post_id
JOIN wp_686591_posts p 
    ON brand.post_id = p.ID
WHERE
    brand.meta_key = 'brand'
    AND price.meta_key = 'selling-price'
    AND p.post_status IN ('publish', 'pending')
GROUP BY brand.meta_value;

关键说明： #

• 使用JOIN关联品牌和售价的元数据，确保每辆车的品牌和售价一一对应
• 关联wp_686591_posts表过滤指定状态的车辆
• CAST(price.meta_value AS DECIMAL(10,2))用于将存储的售价字符串转换为数值类型（如果你的售价是直接存数值可以去掉CAST）
• COUNT(DISTINCT brand.post_id)避免重复计数同一车辆

2. 分别统计publish和pending状态的明细 #

如果需要在同一结果中区分两种状态的统计数据，可以用条件聚合实现：

SELECT
    brand.meta_value AS brand,
    -- 已发布车辆统计
    COUNT(DISTINCT CASE WHEN p.post_status = 'publish' THEN brand.post_id END) AS 'publish_vehicles',
    SUM(CASE WHEN p.post_status = 'publish' THEN CAST(price.meta_value AS DECIMAL(10,2)) END) AS 'publish_total',
    -- 待审核车辆统计
    COUNT(DISTINCT CASE WHEN p.post_status = 'pending' THEN brand.post_id END) AS 'pending_vehicles',
    SUM(CASE WHEN p.post_status = 'pending' THEN CAST(price.meta_value AS DECIMAL(10,2)) END) AS 'pending_total',
    -- 总计
    COUNT(DISTINCT brand.post_id) AS 'total_vehicles',
    SUM(CAST(price.meta_value AS DECIMAL(10,2))) AS 'total_price'
FROM wp_686591_postmeta brand
JOIN wp_686591_postmeta price 
    ON brand.post_id = price.post_id
JOIN wp_686591_posts p 
    ON brand.post_id = p.ID
WHERE
    brand.meta_key = 'brand'
    AND price.meta_key = 'selling-price'
    AND p.post_status IN ('publish', 'pending')
GROUP BY brand.meta_value;

特殊情况处理： #

如果存在车辆未设置售价（即没有selling-price的元数据记录），可以将JOIN wp_686591_postmeta price改为LEFT JOIN wp_686591_postmeta price，并使用COALESCE(SUM(...), 0)将空值转为0，避免总售价显示为NULL。

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