如何在MySQL查询中合并列值？WordPress多周期价格排序方案

嘿，我来帮你搞定这个按统一价格排序的问题！看起来你已经在处理货币转换了，现在需要把不同周期（月度/年度）的价格统一成可比的数值，这样就能顺利排序了对吧？

我分两种常见的存储场景给你解决方案，你可以根据自己的实际情况调整：

场景1：价格周期用单独的元字段存储 #

假设你的帖子里，价格存在meta_key='villa_price'，而这个价格对应的周期（月度/年度）存在另一个meta_key='price_period'（值为monthly或yearly）。我们可以先把所有价格统一转换成月度等价价格，再处理货币转换，最后排序：

SELECT 
    p.*,
    -- 先统一周期为月度价格，再做货币转换
    CASE 
        WHEN period.meta_value = 'yearly' THEN 
            CASE currency.meta_value 
                WHEN 'IDR' THEN (price.meta_value * {$currency_rate['IDR']}) / 12
                WHEN 'USD' THEN (price.meta_value * {$currency_rate['USD']}) / 12
                WHEN 'EUR' THEN (price.meta_value * {$currency_rate['EUR']}) / 12
                ELSE price.meta_value -- 兼容其他未定义的货币
            END
        ELSE -- 月度价格直接转换货币
            CASE currency.meta_value 
                WHEN 'IDR' THEN price.meta_value * {$currency_rate['IDR']}
                WHEN 'USD' THEN price.meta_value * {$currency_rate['USD']}
                WHEN 'EUR' THEN price.meta_value * {$currency_rate['EUR']}
                ELSE price.meta_value
            END
    END AS normalized_monthly_price
FROM 
    wp_posts p
JOIN 
    wp_postmeta price ON p.ID = price.post_id AND price.meta_key = 'villa_price'
JOIN 
    wp_postmeta period ON p.ID = period.post_id AND period.meta_key = 'price_period'
JOIN 
    wp_postmeta currency ON p.ID = currency.post_id AND currency.meta_key = 'currency'
WHERE 
    p.post_type = 'villa' -- 替换成你的自定义文章类型
    AND p.post_status = 'publish'
ORDER BY 
    normalized_monthly_price ASC; -- 升序排序，改成DESC就是从高到低

场景2：不同周期的价格用不同的元键存储 #

如果你的帖子里，月度价格存在meta_key='villa_price_monthly'，年度价格存在meta_key='villa_price_yearly'，那我们可以先判断哪个价格字段存在，再统一转换成可比数值：

SELECT 
    p.*,
    CASE
        -- 优先使用月度价格（如果存在）
        WHEN monthly_price.meta_id IS NOT NULL THEN
            CASE currency.meta_value 
                WHEN 'IDR' THEN monthly_price.meta_value * {$currency_rate['IDR']}
                WHEN 'USD' THEN monthly_price.meta_value * {$currency_rate['USD']}
                WHEN 'EUR' THEN monthly_price.meta_value * {$currency_rate['EUR']}
                ELSE monthly_price.meta_value
            END
        -- 没有月度价格就用年度价格转成月度等价
        ELSE
            CASE currency.meta_value 
                WHEN 'IDR' THEN (yearly_price.meta_value * {$currency_rate['IDR']}) / 12
                WHEN 'USD' THEN (yearly_price.meta_value * {$currency_rate['USD']}) / 12
                WHEN 'EUR' THEN (yearly_price.meta_value * {$currency_rate['EUR']}) / 12
                ELSE yearly_price.meta_value / 12
            END
    END AS normalized_monthly_price
FROM 
    wp_posts p
LEFT JOIN 
    wp_postmeta monthly_price ON p.ID = monthly_price.post_id AND monthly_price.meta_key = 'villa_price_monthly'
LEFT JOIN 
    wp_postmeta yearly_price ON p.ID = yearly_price.post_id AND yearly_price.meta_key = 'villa_price_yearly'
JOIN 
    wp_postmeta currency ON p.ID = currency.post_id AND currency.meta_key = 'currency'
WHERE 
    p.post_type = 'villa'
    AND p.post_status = 'publish'
    -- 确保帖子至少有一个价格字段
    AND (monthly_price.meta_id IS NOT NULL OR yearly_price.meta_id IS NOT NULL)
ORDER BY 
    normalized_monthly_price ASC;

额外提示：WordPress里安全使用的小技巧 #

直接拼接变量到SQL里有注入风险，建议用WordPress的$wpdb->prepare来处理参数，比如这样：

$normalized_query = $wpdb->prepare(
    "SELECT p.*,
        CASE 
            WHEN period.meta_value = 'yearly' THEN 
                CASE currency.meta_value 
                    WHEN 'IDR' THEN (price.meta_value * %f) / 12
                    WHEN 'USD' THEN (price.meta_value * %f) / 12
                    WHEN 'EUR' THEN (price.meta_value * %f) / 12
                    ELSE price.meta_value
                END
            ELSE
                CASE currency.meta_value 
                    WHEN 'IDR' THEN price.meta_value * %f
                    WHEN 'USD' THEN price.meta_value * %f
                    WHEN 'EUR' THEN price.meta_value * %f
                    ELSE price.meta_value
                END
        END AS normalized_monthly_price
    FROM wp_posts p
    JOIN wp_postmeta price ON p.ID = price.post_id AND price.meta_key = 'villa_price'
    JOIN wp_postmeta period ON p.ID = period.post_id AND period.meta_key = 'price_period'
    JOIN wp_postmeta currency ON p.ID = currency.post_id AND currency.meta_key = 'currency'
    WHERE p.post_type = 'villa' AND p.post_status = 'publish'
    ORDER BY normalized_monthly_price ASC",
    $currency_rate['IDR'],
    $currency_rate['USD'],
    $currency_rate['EUR'],
    $currency_rate['IDR'],
    $currency_rate['USD'],
    $currency_rate['EUR']
);

$sorted_posts = $wpdb->get_results($normalized_query);

你只需要把代码里的元键、文章类型换成你自己的实际值就可以啦！

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