这是我的“产品”表WHERE sku RLIKE 'IP-205-1067-16' or sku IN ('205-d-SC55G','205-d-RC099G','205-d-SC45G','205-d-RC099G')：
+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+
| sku | price_1 | full_color_imprint_id | embroidery_imprint_id | screen_print_imprint_id | laser_engraving_imprint_id |
+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+
| IP-205-1067-16 | 1.55556 | NULL | NULL | 63276 | 32539 |
| IP-205-1067-16-39423495 | 1.55556 | NULL | NULL | 63276 | 32539 |
| IP-205-1067-16-1272347 | 1.55556 | NULL | NULL | 63276 | 32539 |
| IP-205-1067-16-56185 | 1.55556 | NULL | NULL | 63276 | 32539 |
| IP-205-1067-16-1706399150 | 1.55556 | NULL | NULL | 63276 | 32539 |
| 205-d-SC45G | 45 | NULL | NULL | NULL | NULL |
| 205-d-SC55G | 55 | NULL | NULL | NULL | NULL |
| 205-d-RC059G | 0.59 | NULL | NULL | NULL | NULL |
| 205-d-RC099G | 0.99 | NULL | NULL | NULL | NULL |
+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+
这是我的“ imprint_locations”表
WHERE imprint_method_id IN (63276,32539)：+--------+-------------------+------------+-------------+--------------+
| id | imprint_method_id | name | setup_sku | running_sku |
+--------+-------------------+------------+-------------+--------------+
| 809128 | 32539 | Body Right | 205-d-SC45G | 205-d-RC059G |
| 809129 | 32539 | Body Left | 205-d-SC45G | 205-d-RC059G |
| 808288 | 63276 | Body Right | 205-d-SC55G | 205-d-RC099G |
| 808289 | 63276 | Body Left | 205-d-SC55G | 205-d-RC099G
+--------+-------------------+------------+-------------+--------------+
这是我想要达到的最终结果：
+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+
| sku | embroidery_imprint_id_setup_sku_price_1 | embroidery_imprint_id_running_sku_price_1 | screen_print_imprint_id_setup_sku_price_1 | screen_print_imprint_id_running_sku_price_1 | laser_engraving_imprint_id_setup_sku_price_1 | laser_engraving_imprint_id_running_sku_price_1 |
+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+
| IP-205-1067-16 | NULL | NULL | 55 | 0.99 | 45 | 0.59 |
| IP-205-1067-16-39423495 | NULL | NULL | 55 | 0.99 | 45 | 0.59 |
| IP-205-1067-16-1272347 | NULL | NULL | 55 | 0.99 | 45 | 0.59 |
| IP-205-1067-16-56185 | NULL | NULL | 55 | 0.99 | 45 | 0.59 |
| IP-205-1067-16-1706399150 | NULL | NULL | 55 | 0.99 | 45 | 0.59 |
+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+
您可以看到产品表的screen_print_imprint_id与imprint_locations表中的imprint_method_id相匹配。
最佳答案
您需要使用product表作为中间关系表与imprint_locations表连接两次。
您可以为要链接的每个ID字段使用单独的联接，也可以使用MySQL pivot table中的方法联接一次，并将价格转入相应的列。
SELECT DISTINCT p1.sku,
MAX(IF(p1.embroidery_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS embroidery_imprint_id_setup_sku_price_1,
MAX(IF(p1.embroidery_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS embroidery_imprint_id_running_sku_price_1,
MAX(IF(p1.screen_print_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS screen_print_imprint_id_setup_sku_price_1,
MAX(IF(p1.screen_print_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS screen_print_imprint_id_running_sku_price_1,
MAX(IF(p1.laser_engraving_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS laser_engraving_imprint_id_setup_sku_price_1,
MAX(IF(p1.laser_engraving_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS laser_engraving_imprint_id_running_sku_price_1
FROM product AS p1
JOIN imprint_locations AS l ON l.imprint_method_id IN (p1.embroidery_imprint_id, p1.screen_print_imprint_id, p1.laser_engraving_imprint_id)
JOIN product AS p2 ON p2.sku IN (l.running_sku, l.setup_sku)
GROUP BY p1.sku
DEMO