mysql过滤器_Mysql查询 - 如何包含“过滤器”

我有两个数据库表，两个行都很多 . 第一个表格包含产品概述，第二个表格包含产品的附加信息 . 表格如下：

#Products

+ ---------- + ------ + ------------ +

| Product_Id | Status | EAN |

+ ---------- + ------ + ------------ +

| 1 | 1 | 0123456789 |

| 2 | 1 | 9876543210 |

| 3 | 1 | 6548214656 |

| 4 | 2 | 245511411241 |

| 5 | 1 | 8888888888 |

| etc. | etc . | etc. |

+ ---------- + ------ + ------------ +

#Info

+ ------- + ------------ + --------- + ---------- +

| Info_Id | EAN | Info_Type | Info_Value |

+ ------- + ------------ + --------- + ---------- +

| 1 | 0123456789 | brand | brand1 |

| 2 | 0123456789 | type | type1 |

| 3 | 0123456789 | price | 0.00 |

| 4 | 9876543210 | brand | brand6 |

| 5 | 9876543210 | type | type3 |

| 6 | 9876543210 | price | 15.00 |

| 7 | 6548214656 | brand | brand34 |

| 8 | 6548214656 | type | type1 |

| 9 | 6548214656 | price | 99.00 |

| 10 | 245511411241 | brand | brand324 |

| 11 | 245511411241 | type | type1 |

| 12 | 245511411241 | price | 98.00 |

| 13 | 8888888888 | brand | brand1 |

| 14 | 8888888888 | price | 9.00 |

| 14 | 8888888888 | type | type4 |

| etc. | etc. | etc. | etc. |

+ ------- + ------------ + --------- + ---------- +

通过以下查询，我可以通过匹配EAN来找到与产品对应的附加信息：

select i.EAN

, p.ProductStatus

, max(case info_type when 'brand' then info_value end) as brand

, max(case info_type when 'type' then info_value end) as [type]

, max(case info_type when 'price' then info_value end) as price

from #Info i

inner join #Products p on p.ean = i.ean

WHERE p.Status=1

group by i.EAN, p.ProductStatus ORDER BY P.Product_id DESC LIMIT 10

这给我提供了下表：

+ ------------ + ------ + -------- + ----- + ----- +

| EAN | Status | brand | type | price |

+ ------------ + ------ + -------- + ----- + ----- +

| 0123456789 | 1 | brand1 | type1 | 0.00 |

| 6548214656 | 1 | brand34 | type1 | 99.00 |

| 9876543210 | 1 | brand6 | type3 | 15.00 |

| 8888888888 | 1 | brand1 | type4 | 9.00 |

| etc. (10 products) |

+ ------------ + ------ + -------- + ----- + ----- +

但是，现在我希望能够过滤特定的品牌，类型或价格 . 例如，我只想选择价格大于50的产品(Info_Type = price AND Info_Value> 50.00)，其次品牌是特定品牌，比如说“brand1”(Info_Type = brand AND Info_Value = brand1) . 有人能告诉我如何将其包含在我的查询中吗？尝试了很多东西，最接近我得到的结果是以下查询：

select i.EAN

, p.ProductStatus

, max(case when (info_type = 'brand' AND info_value='brand1') then info_value end) as brand

, max(case info_type when 'type' then info_value end) as [type]

, max(case info_type when 'price' then info_value end) as price

from #Info i

inner join #Products p on p.ean = i.ean

WHERE p.Status=1

group by i.EAN, p.ProductStatus ORDER BY P.Product_id DESC LIMIT 10

但是，这会产生以下结果：

+ ------------ + ------ + -------- + ----- + ----- +

| EAN | Status | brand | type | price |

+ ------------ + ------ + -------- + ----- + ----- +

| 0123456789 | 1 | brand1 | type1 | 0.00 |

| 6548214656 | 1 | NULL | type1 | 99.00 |

| 9876543210 | 1 | NULL | type3 | 15.00 |

| 8888888888 | 1 | brand1 | type4 | 9.00 |

| etc. (10 products) |

+ ------------ + ------ + -------- + ----- + ----- +

我想看到以下结果：

+ ------------ + ------ + -------- + ----- + ----- +

| EAN | Status | brand | type | price |

+ ------------ + ------ + -------- + ----- + ----- +

| 0123456789 | 1 | brand1 | type1 | 0.00 |

| 8888888888 | 1 | brand1 | type4 | 9.00 |

| etc. (10 products) |

+ ------------ + ------ + -------- + ----- + ----- +

谁帮助我？ :)