我有一个包含产品的表格:Insert into products (products_id, products_price) Values(1, '1.25')
还有一个包含产品名称的表格
Insert into products_descriptions (products_id, language_id, products_name) Values (1, 1, 'shoe');
Insert into products_descriptions (products_id, language_id, products_name) Values (1, 2, 'chaussure');
现在我想在一条记录中选择产品详细信息,例如:products_id, products_price, language_id-1, products_name-1, language_id-2, products_name-2
所以结果看起来像:
+---------------------------------------------------------------------------------------------------+
| products_id | products_price | language_id-1 | products_name-1 | language_id-2 | products_name-2 |
+---------------------------------------------------------------------------------------------------+
| 1 | '1.25' | 1 | 'shoe' | 2 | 'chaussure' |
+---------------------------------------------------------------------------------------------------+
所以基本上我想将language_id
添加到products_descriptions
列名称中,以识别language
和products_name
并在一行中获取结果。
我一直在解决这个问题,所以我可以使用一些帮助。
GROUP_CONCAT
可能符合您的要求:
SELECT
p.products_id,
p.products_price,
GROUP_CONCAT(CONCAT(pd.language_id, ' - ', pd.products_name)
ORDER BY pd.language_id) AS product_names
FROM products p
INNER JOIN products_descriptions pd
ON p.products_id = pd.products_id
GROUP BY
p.products_id,
p.products_price;
编辑:
如果您打算从应用程序中使用预期查询的结果,则应使用此版本:
SELECT
p.products_id,
p.products_price,
pd.language_id,
pd.products_name
FROM products p
INNER JOIN products_descriptions pd
ON p.products_id = pd.products_id
ORDER BY
p.products_id,
p.products_price;
然后,只需从应用程序语言迭代结果集,一次一个产品。