mysql - SQL合并具有相同通配符值的记录

我有一个MySQL Wordpress数据库，正在处理结构不太好的postmeta表。它具有四列：“ meta_id，post_id，meta_key，meta_value”。这是我正在使用的查询：

SELECT  DISTINCT *
        FROM wp_10_postmeta as pm
        JOIN wp_10_posts AS p ON pm.post_id = p.ID
        WHERE (meta_key LIKE 'personnel_%_last_name' OR
                meta_key LIKE 'personnel_%_first_name' OR
                meta_key LIKE 'personnel_%_title' OR
                meta_key LIKE 'personnel_%_alias' OR
                meta_key LIKE 'webpages_%_title' OR
                meta_key LIKE 'webpages_%_alias' OR
                meta_key LIKE 'departments_%_name' OR
                meta_key LIKE 'departments_%_alias')
            AND post_status = 'publish' AND meta_value LIKE '%john%' AND meta_value LIKE '%smith%'

问题：如果我搜索“ john”或“ Smith”，此查询效果很好，但如果我搜索“ John Smith”，则返回空。有没有一种方法可以组合“ personnel _％_ last_name”和“ personnel _％_ first_name”，然后进行比较？

最佳答案

我猜您想在多个字段中查找匹配项。为此，请尝试使用group by：

    SELECT p.*
    FROM wp_10_postmeta as pm JOIN
         wp_10_posts AS p
         ON pm.post_id = p.ID
    WHERE (meta_key LIKE 'personnel_%_last_name' OR
            meta_key LIKE 'personnel_%_first_name' OR
            meta_key LIKE 'personnel_%_title' OR
            meta_key LIKE 'personnel_%_alias' OR
            meta_key LIKE 'webpages_%_title' OR
            meta_key LIKE 'webpages_%_alias' OR
            meta_key LIKE 'departments_%_name' OR
            meta_key LIKE 'departments_%_alias')
        AND p.post_status = 'publish'
    GROUP BY p.id
    HAVING SUM(meta_value LIKE '%john%') > 0 AND SUM(meta_value LIKE '%smith%') > 0;

关于mysql - SQL合并具有相同通配符值的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25902013/