我有两个数据库表,两个行都很多 . 第一个表格包含产品概述,第二个表格包含产品的附加信息 . 表格如下:
#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) |
+ ------------ + ------ + -------- + ----- + ----- +
谁帮助我? :)