我有两张table,一张是订单,一张是产品
ORDERS TABLE
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+
| order_id | order_by | status | created_at | product1 | product2 | delivery_date | pickup |
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+
| 1 | Maria | 0 | 2020-07-19 00:00:00 | 1 | 3 | 2020-07-17 00:00:00 | 0 |
| 2 | Joao | 0 | 2020-07-20 00:00:00 | 2 | 0 | 2020-07-20 00:00:00 | 0 |
| 3 | Jose | 1 | 2020-07-20 00:00:00 | 3 | 0 | 2020-07-20 00:00:00 | 0 |
| 4 | Matias | 0 | 2020-07-19 23:50:40 | 1 | 3 | 2020-07-19 00:00:00 | 0 |
| 5 | Matias | 0 | 2020-07-19 23:50:40 | 1 | 3 | 2020-07-19 00:00:00 | 0 |
| 6 | Joaozinho | 0 | 2020-07-19 00:00:00 | 1 | 3 | 2020-07-22 00:00:00 | 0 |
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+
PRODUCTS TABLE
+----+-----------+-------+
| id | name | price |
+----+-----------+-------+
| 1 | Produto 1 | 11 |
| 2 | Produto 2 | 22 |
| 3 | Produto 3 | 33 |
| 4 | Produto 4 | 44 |
| 5 | Produto 5 | 55 |
+----+-----------+-------+
EXPECTED OUTPUT
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+-----------+----------+-----------+----------+
| order_id | order_by | status | created_at | product1 | product2 | delivery_date | pickup | p1_name | p1_price | p2_name | p2_price |
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+-----------+----------+-----------+----------+
| 1 | Maria | 0 | 2020-07-19 00:00:00 | 1 | 3 | 2020-07-17 00:00:00 | 0 | Produto 1 | 11 | Produto 3 | 33 |
| 2 | Joao | 0 | 2020-07-20 00:00:00 | 2 | 0 | 2020-07-20 00:00:00 | 0 | Produto 2 | 22 | null | null |
| 3 | Jose | 1 | 2020-07-20 00:00:00 | 3 | 0 | 2020-07-20 00:00:00 | 0 | Produto 3 | 33 | null | null |
| 4 | Matias | 0 | 2020-07-19 23:50:40 | 1 | 3 | 2020-07-19 00:00:00 | 0 | Produto 1 | 11 | Produto 3 | 33 |
| 5 | Matias | 0 | 2020-07-19 23:50:40 | 1 | 3 | 2020-07-19 00:00:00 | 0 | Produto 1 | 11 | Produto 3 | 33 |
| 6 | Joaozinho | 0 | 2020-07-19 00:00:00 | 1 | 3 | 2020-07-22 00:00:00 | 0 | Produto 1 | 11 | Produto 3 | 22 |
+----------+-----------+--------+---------------------+----------+----------+---------------------+--------+------+-----------+-------+------------------+
我需要在orders表和price列的每个产品(product1,product2..)上使用不同的名称(product1\u name,product2\u name..)留下连接
2条答案
按热度按时间woobm2wo1#
添加product2应该没那么难,只是作为一种练习。。。
wxclj1h52#
你需要两个
join
不是吗products
表格:如果
product1
始终可用,您可以先打开left join
到一个inner join
,这使得查询的效率略高。