您可以使用单个查询从数据库中获取所有信息，并在应用程序级别根据您的需要构造数据，下面的查询将多次返回每个产品，但具有不同的标签/类别。

select p.product_id, p.name, t.tag_id, t.name tag_name, c.category_id, c.cat_name 
from product p
join products_tags as pt on pt.product_id = p.product_id
join tags t on pt.tag_id = t.tag_id
join products_categories as pc on pc.product_id = p.product_id
join categories c on pc.category = c.category_id
order by p.product_id

伪码

products = []
tags = []
categories = []
var productObj= {};
var product_id =false;
for each product
    /**
    * If product_id != product.id from loop iteration it means this is different product from previous iteration, Push this product object in `products[]`
    */
    if(product.id != product_id){
        products[product.id] = product;
        product_id = product.id;
    }
    /**you can put additional check here before insert in array if value is not present  `products[product.id][tags]` */
    products[product.id][tags][] = tag_name;

    /**you can put additional check here before insert in array if value is not present  `products[product.id][categories]` */ 
    products[product.id][categories][]= cat_name;
end for each

产品的示例对象/数据结构

[ 
    1 => [
    name => A good smartphone,
    categories => [Tech, Smartphone],
    tags => [smartphone, some-smartphone-brand, 4g]
    ],  
    2 => [
    name: Another product,
    categories => [Cat1, Cat2],
    tags => [Tag1, Tag2, .....]
    ],
.....
]

您可以尝试这样做（它依次使用带有标记和类别的联接，并通过列表类型来区分它们）：

select *, 'CAT' as table_type
from products p left join products_categories pc left join categories c  
ON p.product_id = pc.product_id and pc.category_id = c.category_id
UNION ALL
select *, 'TAG' as table_type
from products p left join products_tags pt left join tags t  
ON p.product_id = pt.product_id and pt.tag_id = t.tag_id
order by p.product_id