php - 如何在 mysql 查询中获取其品牌的唯一零件号？

Question

我的数据库中有以下表格，我想获得唯一的零件号及其品牌名称。以下所有表格均基于 EAV 模型

表 1 的零件号(例如 - 8004)。

entity_id是唯一约束

select * from catalog_product_entity_varchar where attribute_id = 961 and value = '8004';

+------------+----------------+------------ +------------+------------+------+
|值_id | entity_type_id |属性编号 |店铺编号 |实体编号 |值(value) |
+------------+----------------+------------+----- -----+------------+--------+
| 19507400 | 10 |第961章0 | 39214 | 8004 |
| 19507401 | 10 |第961章0 | 281155 | 8004 |
| 19507402 | 10 |第961章0 | 1926249 | 8004 |
| 64825324 | 10 |第961章0 | 11892287 | 8004 |
| 168417193 | 10 |第961章0 | 22721887 | 8004 |
+------------+----------------+------------+----- -----+------------+--------+


品牌代码表 2 -

select * from catalog_product_entity_int where attribute_id = 953 and entity_id in (39214,281155,1926249,11892287,22721887);

+------------+----------------+------------+ ----------+------------+------+
|值_id | entity_type_id |属性编号 |店铺编号 |实体编号 |值(value) |
+------------+----------------+------------+----- ----+------------+------+
| 5401700 | 10 |第953章0 | 39214 | 1050 |
| 5401701 | 10 |第953章0 | 281155 | 1109 |
| 5401702 | 10 |第953章0 | 1926249 | 1082|
| 21106883 | 10 |第953章0 | 11892287 | 1109 |
| 87135500 | 10 |第953章0 | 22721887 | 1109 |
+------------+----------------+------------+----- ----+------------+------+


表 3 基于品牌代码的实际品牌名称。

select * from eav_attribute_option_value where option_id in (1050,1109,1082);

+------------+------------+--------+-------- --------------+
|值_id |选项编号 |店铺编号 |值(value) |
+------------+------------+----------+-------------- ----------+
| 15222 | 1050 | 0 |三星|
| 13070 | 1082| 0 |惠而浦 |
| 15317 | 1109 | 0 |通用电气 |
+------------+------------+----------+-------------- ----------+


预期输出-

Answer 1

我认为这就是您正在寻找的查询

SELECT GROUP_CONCAT(b.entity_id SEPARATOR ',') as entity_ids,p.value as part_no,GROUP_CONCAT(b.value SEPARATOR ',') as brand_code, GROUP_CONCAT(bn.value SEPARATOR ',') as brand_name FROM catalog_product_entity_varchar p RIGHT JOIN catalog_product_entity_int b ON p.entity_id  = b.entity_id  LEFT JOIN eav_attribute_option_value bn ON b.value = bn.option_id WHERE p.attribute_id = '961' GROUP BY b.entity_type_id