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

骑猪看日落 2022-10-31 13:39 121阅读 0赞

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

\#Products

\+ ---------- + ------ + ------------ +

| Product\_Id | Status | EAN |

\+ ---------- + ------ + ------------ +

| 1 | 1 | 0123456789 |

| 2 | 1 | 9876543210 |

| 3 | 1 | 6548214656 |

| 4 | 2 | 245511411241 |

| 5 | 1 | 8888888888 |

| etc. | etc . | etc. |

\+ ---------- + ------ + ------------ +

\#Info

\+ ------- + ------------ + --------- + ---------- +

\+ ------- + ------------ + --------- + ---------- +

| 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

这给我提供了下表：

\+ ------------ + ------ + -------- + ----- + ----- +

\+ ------------ + ------ + -------- + ----- + ----- +

| 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

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

\+ ------------ + ------ + -------- + ----- + ----- +

\+ ------------ + ------ + -------- + ----- + ----- +