如何用INNER JOIN或子查询高效合并SQL查询获取产品关联数据？

问题背景 #

现有数据库表结构如下：

1. Products（产品表） #

2. Tags（标签表） #

3. ProductTags（产品标签桥接表） #

4. ProductVariants（产品变体表） #

5. ProductMedia（产品媒体表） #

需要获取单条结构化结果，格式如下：

Result = {
  ProductID: 1,
  ProductName: "Product1",
  ProductMedia: [{ ProductMediaPath: '/img/someimg.jpg' }, { ProductMediaPath: '/img/someotherimg.jpg' }],
  ProductVariants: [所有变体列表],
  ProductTags: [所有标签列表]
}

当前通过4次独立查询获取数据：

1. SELECT * FROM Products WHERE ProductID = ? LIMIT 1
2. SELECT ProductMediaPath FROM ProductMedia WHERE ProductID = ?
3. SELECT Tags.TagName FROM Tags INNER JOIN ProductTags ON ProductTags.TagID=Tags.TagID WHERE ProductTags.ProductID = ?
4. SELECT * FROM ProductVariants WHERE ProductID = ?

需求：使用INNER JOIN或子查询合并为更高效的SQL语句，直接得到所需结构。

解决方案 #

1. 适用于支持GROUP_CONCAT的数据库（如MySQL） #

通过子查询聚合关联数据，查询后在应用层解析为数组：

SELECT
  p.ProductID,
  p.ProductName,
  -- 聚合媒体路径，用分隔符分隔
  (SELECT GROUP_CONCAT(DISTINCT pm.ProductMediaPath SEPARATOR '|') 
   FROM ProductMedia pm 
   WHERE pm.ProductID = p.ProductID) AS ProductMedia,
  -- 聚合标签名，去重桥接表重复数据
  (SELECT GROUP_CONCAT(DISTINCT t.TagName SEPARATOR '|') 
   FROM ProductTags pt 
   JOIN Tags t ON pt.TagID = t.TagID 
   WHERE pt.ProductID = p.ProductID) AS ProductTags,
  -- 聚合变体字段为结构化字符串
  (SELECT GROUP_CONCAT(DISTINCT CONCAT('VariantID:', v.VariantID, ',VariantName:', v.VariantName) SEPARATOR '||') 
   FROM ProductVariants v 
   WHERE v.ProductID = p.ProductID) AS ProductVariants
FROM Products p
WHERE p.ProductID = ?
LIMIT 1;

注意：选择不会与字段内容冲突的分隔符，查询后在应用层分割字符串并转换为数组/对象结构。

2. 适用于支持JSON函数的数据库（如MySQL 8.0+、PostgreSQL） #

直接用JSON函数生成目标结构化结果，无需应用层额外解析：

MySQL 8.0+版本

SELECT
  p.ProductID,
  p.ProductName,
  -- 生成媒体路径的JSON数组
  JSON_ARRAYAGG(DISTINCT JSON_OBJECT('ProductMediaPath', pm.ProductMediaPath)) AS ProductMedia,
  -- 生成标签的JSON数组
  JSON_ARRAYAGG(DISTINCT t.TagName) AS ProductTags,
  -- 生成变体的JSON对象数组
  JSON_ARRAYAGG(DISTINCT JSON_OBJECT('VariantID', v.VariantID, 'VariantName', v.VariantName)) AS ProductVariants
FROM Products p
LEFT JOIN ProductMedia pm ON p.ProductID = pm.ProductID
LEFT JOIN ProductTags pt ON p.ProductID = pt.ProductID
LEFT JOIN Tags t ON pt.TagID = t.TagID
LEFT JOIN ProductVariants v ON p.ProductID = v.ProductID
WHERE p.ProductID = ?
GROUP BY p.ProductID, p.ProductName;

说明：用LEFT JOIN确保无关联数据时主产品信息仍能返回，DISTINCT去除重复的关联记录。

PostgreSQL版本

SELECT
  p.ProductID,
  p.ProductName,
  -- 媒体路径数组
  ARRAY_AGG(DISTINCT pm.ProductMediaPath) AS ProductMedia,
  -- 标签数组
  ARRAY_AGG(DISTINCT t.TagName) AS ProductTags,
  -- 变体对象数组
  ARRAY_AGG(DISTINCT json_build_object('VariantID', v.VariantID, 'VariantName', v.VariantName)) AS ProductVariants
FROM Products p
LEFT JOIN ProductMedia pm ON p.ProductID = pm.ProductID
LEFT JOIN ProductTags pt ON p.ProductID = pt.ProductID
LEFT JOIN Tags t ON pt.TagID = t.TagID
LEFT JOIN ProductVariants v ON p.ProductID = v.ProductID
WHERE p.ProductID = ?
GROUP BY p.ProductID, p.ProductName;

效率说明 #

• 合并查询仅需一次数据库往返，减少网络开销，比四次独立查询更高效
• 确保ProductID、TagID等关联字段已建立索引，可进一步提升查询速度
• 大数据量场景下，JSON聚合函数的性能优于字符串拼接后解析

内容的提问来源于stack exchange，提问作者visdev