这是我的5个表。
产品
- ID(PK)
- 名称
描述
- ID(PK)
- 身体
价格
- ID(PK)
- 货币
product_description
- ID(PK)
- product_id
- Description_id
product_price
- ID(PK)
- product_id
- Price_id
表product
,description
,price
是存储的实际数据。
和表product_description
和product_price
是参考表。
我的预期查询结果。
product_id | product_name | description_body | price_currency
当前表的数据
product
+----+----------------+
| id | name |
+----+----------------+
| 1 | first product |
| 2 | second product |
| 3 | third product |
+----+----------------+
description
+----+------------+
| id | body |
+----+------------+
| 1 | first desc |
| 2 | second des |
| 3 | third desc |
+----+------------+
price
+----+-------------+
| id | currency |
+----+-------------+
| 1 | first cur |
| 2 | second cur2 |
| 3 | third cur |
+----+-------------+
product_description
+----+------------+----------------+
| id | product_id | description_id |
+----+------------+----------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------------+----------------+
product_price
+----+------------+----------+
| id | product_id | price_id |
+----+------------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------------+----------+
查询
SELECT product.*, description.body, price.currency FROM product
LEFT JOIN product_description ON product.id = product_description.product_id
LEFT JOIN product_price ON product.id = product_price.product_id
LEFT JOIN description ON description.id = product_description.description_id
LEFT JOIN price ON price.id = product_price.price_id
结果
+----+----------------+------------+-------------+
| id | name | body | currency |
+----+----------------+------------+-------------+
| 1 | first product | first desc | first cur |
| 2 | second product | second desc| second cur2 |
| 3 | third product | third desc | third cur |
+----+----------------+------------+-------------+
在那一刻,如果我插入 product_description
又有一个具有product_id=1
并重新查询的行,它显示了许多行,即product_id=2
。
插入product_description
值(4、2、1)
product_description
+----+------------+----------------+
| id | product_id | description_id |
+----+------------+----------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 2 | 1 |
+----+------------+----------------+
加入查询后。
+----+----------------+------------+-------------+
| id | name | body | currency |
+----+----------------+------------+-------------+
| 1 | first product | first desc | first cur |
| 2 | second product | second desc | second cur2 |
| 2 | second product | first desc | second cur2 |
| 3 | third product | third desc | third cur |
+----+----------------+------------+-------------+
您知道,由于product=2
行的计数为2个product_description
,结果打印全部。
,但我只想持续一个包含product=2
的行。
也许限制或独特是有用的,我不知道这是可能的。
我可以在加入查询中使用限制或不同吗?
您可以尝试使用相关子查询
SELECT product.*, description.body, price.currency FROM product
LEFT JOIN
( select * from product_description
where product_description.id in (select max(id) from product_description b
where product_description.product_id=b.product_id)
) as x ON product.id = x.product_id
LEFT JOIN product_price ON product.id = product_price.product_id
LEFT JOIN description ON description.id = x.description_id
LEFT JOIN price ON price.id = product_price.price_id