在多个联接行中的多个字段上进行MySQL过滤器(AND)查询 [英] Mysql filter (AND) query on multiple fields in multiple joined rows

问题描述

我创建了一个查询，该查询返回具有其字段和值的产品列表.现在，我想在字段中搜索某个值，并获取一个与此搜索查询匹配的结果列表.问题是我需要AND构造，因此fieldx值必须类似于％car％，fieldy值必须类似于％chrome％.

I have created a query which returns a list of products with their fields and values. Now I want to search through fields for a certain value and get a resultlist matching this search query. The problem is that I want an AND construction, so fieldx value must be like %car% and fieldy value must be like %chrome%.

这是我的查询和结果集的示例:

Here`s an example of my query and the resultset:

查询

SELECT p.id as product,pf.field_name,pfv.field_value
FROM product p
JOIN field pf ON pf.product_id = p.product_id
JOIN field_val pfv ON pfv.field_id = pf.field_id

结果集

product | field_name | field_value
pr1     | meta_title | Example text
pr1     | meta_kw    | keyword,keyword1
pr1     | prod_name  | Product 1
pr2 ....

因此，考虑到上面的查询和结果集，我想执行以下操作: 查询meta_title包含"Example"且prod_name包含"Product"的所有产品.之后，我想对结果进行分组，以便仅在两个搜索查询都匹配的情况下返回产品.

So with the above query and resultset in mind I want to do the following: Query all products where meta_title contains 'Example' and where prod_name contains 'Product'. After that, I want to group the results so that only products are returned where both search queries matches.

我尝试了所有可以考虑的事情，并针对相同的问题尝试了许多解决方案，但是我认为我的与众不同，因为我需要字段名称上的AND匹配以及多行中的值.

I tried everything I could think off and I have tried many solutions on kind of the same questions, but I think mine is different because I need the AND match on the field name as well the value over multiple rows.

例如，我尝试将其添加为WHERE子句:

For example, I tried adding this as WHERE clause:

WHERE
(field_name = 'meta_title' AND field_value LIKE '%Example%') AND 
(field_name = 'prod_name' AND field_value LIKE '%Product%')

显然，这是行不通的，因为在meta_title上的第一个位置之后，其他字段名都没有结果了.但是将WHERE更改为OR不会给我想要的结果.

Obviously this won`t work because after the first where on meta_title there is no result left for other field names. But changing the WHERE to OR would not give me the desired result.

我也尝试过HAVING，但结果似乎相同.

I also tried with HAVING but seems like same result.

任何人都知道如何解决这个问题，或者这是不可能的吗?

Anyone an idea how to solve this, or is this just not possible?

推荐答案

一个相对简单的方法是使用group by和having:

A relatively simple way to do this is to use group by and having:

SELECT p.id as product
FROM product p JOIN
     field pf
     ON pf.product_id = p.product_id JOIN
     field_val pfv
     ON pfv.field_id = pf.field_id
WHERE (field_name = 'meta_title' AND field_value LIKE '%Example%') OR 
      (field_name = 'prod_name' AND field_value LIKE '%Product%')
GROUP BY p.id
HAVING COUNT(DISTINCT field_name) = 2;

通过修改HAVING子句(并可能删除WHERE)，可以为存在和不存在不同字段表达许多不同的逻辑.例如，> 0将是OR，而= 1将是一个值或另一个值，但不能同时是两个值.

By modifying the HAVING clause (and perhaps removing the WHERE), it is possible to express lots of different logic for the presence and absence of different fields. For instance > 0 would be OR, and = 1 would be one value or the other, but not both.

这篇关于在多个联接行中的多个字段上进行MySQL过滤器(AND)查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！