从多个表中选择-一对多关系 [英] Select from multiple tables - One to Many relation

问题描述

我有这样的表:

餐桌产品

[ID |名称]

表格图像

[Product_Id |网址|订单号]

[ Product_Id | Url | Ordernumber]

价格

[Product_Id |组合|货币|价格]

[ Product_Id | Combination | Currency | Price]

表数量

[Product_Id |组合|数量]

[ Product_Id | Combination | Quantity]

表产品与其他表是一对多关系.我需要查询表并得到类似这样的结果(伪数组):

Table Product is in relation one-to-many with other tables. I need to query the table and result something like this (pseudo-array):

[
    ProductId: 1,
    Name: 'Sample product',
    Images: [
        [url, 1],
        [url, 2]
    ],
    Prices: [
        [aaa, USD, 50.00],
        [aaa, EUR, 50.00],
        [bbb, USD, 59.00],
        [bbb, EUR, 59.00]
    ],
    Quantities: [
        [aaa, 5],
        [bbb, 3]
    ]
]

我现在的操作方式如下: 我查询所有产品，列出其ID，然后使用WHERE IN子句查询每个表(图像，价格，数量).当我拥有所有数据时，我开始解析php中的表以获得所需的结构.

The way I'm doing it now is as follows: I query all the products, list their id's, and then query each table (images,prices,quantities) with WHERE IN clause. When I have all the data I start to parse the tables in php to get desired structure.

我想知道是否有更好的方法来提取这些数据，我有很多这样的表，并且为它们中的每一个创建配置解析器有点麻烦和成问题. mysql是否有可能会给我带来一些负担?

I wonder if there is some better way to extract those data, I have many different tables like this and creating configuration parser for each of them is a bit messy and problematic. Is there a possibility that mysql will take some burden from me?

谢谢

推荐答案

此查询将为您解决问题:

This query will do the trick for you:

SELECT product.id, product.name, 
(SELECT group_concat(CONCAT('["',images.url, '",',  images.order_number,']')) FROM images WHERE images.product_id = product.id GROUP BY (product.id)) AS IMAGES_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',prices.combination, '","', prices.currency, '",', prices.price,"]" )) FROM prices WHERE prices.product_id = product.id GROUP BY (product.id)) AS PRICE_LIST,
(SELECT GROUP_CONCAT(CONCAT('["',quantites.combination, '",',  quantites.quantity,"]")) FROM quantites WHERE quantites.product_id = product.id GROUP BY (product.id)) AS Quantity_LIST
FROM product WHERE product.id = 1

• 首先获得产品
• 对于每个使用子查询的人，我们都会获得相关图像，而使用concat组，我们可以在一个字段中获取它们
• 价格和数量都一样

这篇关于从多个表中选择-一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！