产品表:
+------------+-------------------+--------------------+
| product_id | product_brand_id | product_group_id|
+------------+-------------------+--------------------+
| 19335 | 10000 | 10002 |
| 19336 | 10000 | 10002 |
| 19358 | 10001 | 10003 |
+------------+-------------------+------------+-------+
因为我在同一个表格中有这样的产品类型和产品品牌。
Picklist_Item表:
+------------+-------------------+----------------+----------------+
| id | picklist_id | picklist_code | piit_name |
+------------+-------------------+----------------+----------------+
| 00001 | 100 | 10000 | TOYOTA |
| 00002 | 100 | 10001 | HONDA |
| 00003 | 101 | 10002 | BODY |
| 00004 | 101 | 10003 | CHEMICAL |
+------------+-------------------+----------------+----------------+
picklist_id告诉这是产品品牌或产品类型
列 picklist_code 是品牌和组 ID
选择列表表:
+-------------------+-------------------+
| picklist_id | pick_name |
+-------------------+-------------------+
| 100 | BRAND |
| 101 | TYPE |
+-------------------+-------------------+
我想要的结果。
+------------+-------------------+----------------+---------------------+-------------+
| product_id | product_brand_id | brand_name | product_group_id | type |
+------------+-------------------+----------------+---------------------+-------------+
| 19335 | 10000 | TOYOTA | 10002 | BODY |
| 19336 | 10000 | TOYOTA | 10002 | BODY |
| 19358 | 10001 | HONDA | 10003 | CHEMICAL |
+------------+-------------------+----------------+---------------------+-------------+
这是我的代码
SELECT Product.product_id , Product.product_brand_id , Picklist_Item.piit_name as Brand_name, Product.product_group_id, Picklist_Item.piit_name as Type
FROM Product
LEFT JOIN Picklist_Item ON Product.product_brand_id = Picklist_Item.picklist_code
LEFT JOIN Picklist_Item ON Product.product_group_id = Picklist_Item.picklist_code
它不起作用。
由于要联接到同一个表两次,因此需要为这两个实例指定不同的别名,以便可以将它们彼此区分开来。此查询将为您提供所需的结果:
SELECT Product.product_id, Product.product_brand_id, p1.piit_name as Brand_name, Product.product_group_id, p2.piit_name as Type
FROM Product
LEFT JOIN Picklist_Item p1 ON Product.product_brand_id = p1.picklist_code
LEFT JOIN Picklist_Item p2 ON Product.product_group_id = p2.picklist_code
在 dbfiddle 上演示