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

问题描述

我有这样的表:

餐桌产品

[ 标识 |姓名]

表格图片

[ 产品 ID |网址 |订单号]

[ Product_Id | Url | Ordernumber]

餐桌价格

[ 产品 ID |组合 |货币 |价格]

[ Product_Id | Combination | Currency | Price]

表格数量

[ 产品 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屋！