比较mysql中相同连接行的两列

本文介绍了比较mysql中相同连接行的两列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我确定答案已经在某处，但我找不到我特别需要的东西.

I'm sure the answer is already in here somewhere, but I can't find what I need in particular.

很简单.我有一个通过 Wordpress(wp_posts 和 wp_postmeta)的一对多表.postmeta 表有任意数量的引用 post 表的行.

It's very simple. I have a one-to-many table via Wordpress (wp_posts and wp_postmeta). The postmeta table has an arbitrary number of rows referencing the posts table.

我需要比较不同 postmeta 行的两个值.

I need to compare two values of different postmeta rows.

示例:

SELECT * FROM wp_posts JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID WHERE ( wp_postmeta.meta_key = 'company' AND wp_postmeta.meta_value LIKE '%Company 01%' ) AND ( wp_postmeta.meta_key = 'description' AND wp_postmeta.meta_value LIKE '%Potato%' )

在上面的示例中，我需要根据同一行中的值检查元键company"和description".但同时，我需要检查另一个元键/值对.

In the example above, I need to check the meta key "company" and "description" against the value within the same row. But at the same time, I need to check another meta key/value pair.

比较这些元键的正确方法是什么?我需要为每个项目比较大约 10 个单独的元键(将来会更多)，所以我预计使用太多子查询会使其变慢.

What is the correct way to compare these meta keys? I will need to compare about 10 seperate meta keys for each item (more in the future) so I would expect using too many subqueries would make it slow.

(仅供参考，这是在 WordPress 之外使用的，无需通知我有关 get_post_meta() 函数的信息)

(FYI this is for use outside of WordPress, no need to inform me about the get_post_meta() function)

推荐答案

虽然在我做进一步调查之前我不会相信性能，但我在原始问题的评论中放置的链接(此处) 似乎有效.Strawberry 已经解释说这种方法效果很好"，所以我会继续这样做.

Although I won't be convinced of the performance until I do some further investigation, the link I put in the comments of the original question (here) seems to work. Strawberry has explained that this method "performs quite well" and so I'll go with that.

我想我会发布这个问题的答案，因为我现在可以使用它.如果有人对此答案有改进(如果有)，请随意单独发布，我肯定会接受最好/最有效的查询.

I guess I will post an answer to this question as I have it working now. If anyone has an improvement to this answer (if there is one) feel free to post it separately, I will definitely accept the best/most efficient query.

最后，这是我现在使用的查询的简化版本，其中删除了大部分不必要的垃圾.这里的主要目标是对每个元字段进行连接并为其分配一个名称，以便该字段包含 WHERE 语句的唯一行:

Finally, here is a simplified version of the query I am now using, with most the unecessary junk pulled out of it. The main objective here is to do a join for each meta field and assign it a name, so that field contains a unique row for the WHERE statement:

set @search_term_1 = '%This string is only found in post meta skills%'; select * from wp_posts post join wp_postmeta meta_comp ON ( meta_comp.post_id = post.ID AND meta_comp.meta_key = 'post_company' ) join wp_postmeta meta_reas ON ( meta_reas.post_id = post.ID AND meta_reas.meta_key = 'post_reason' ) join wp_postmeta meta_skill ON ( meta_skill.post_id = post.ID AND meta_skill.meta_key = 'post_skills' ) join wp_postmeta meta_method ON ( meta_method.post_id = post.ID AND meta_method.meta_key = 'post_method' ) where -- Check post title and description post.post_title LIKE @search_term_1 or post.post_content LIKE @search_term_1 or meta_comp.meta_value LIKE @search_term_1 or meta_reas.meta_value like @search_term_1 or meta_skill.meta_value like @search_term_1 or meta_method.meta_value like @search_term_1 group by post.ID limit 10