要查找产品的扩展分类,我们需要根据数据库结构设计合适的查询。以下是几种常见的解决方案:
假设标准的产品-分类关系是多对多关系,通过中间表连接:
SELECT
p.product_id,
p.product_name,
c.category_id,
c.category_name
FROM
products p
JOIN
product_categories pc ON p.product_id = pc.product_id
JOIN
categories c ON pc.category_id = c.category_id
WHERE
p.product_id = 123; -- 替换为具体产品ID
如果分类有层级结构(父分类-子分类):
SELECT
p.product_id,
p.product_name,
c.category_id,
c.category_name,
parent.category_id AS parent_category_id,
parent.category_name AS parent_category_name
FROM
products p
JOIN
product_categories pc ON p.product_id = pc.product_id
JOIN
categories c ON pc.category_id = c.category_id
LEFT JOIN
categories parent ON c.parent_id = parent.category_id
WHERE
p.product_id = 123;
对于深层次的分类层级(MySQL 8.0+使用CTE):
WITH RECURSIVE category_tree AS (
-- 基础查询:获取产品的直接分类
SELECT
c.category_id,
c.category_name,
c.parent_id,
1 AS level
FROM
categories c
JOIN
product_categories pc ON c.category_id = pc.category_id
WHERE
pc.product_id = 123
UNION ALL
-- 递归查询:获取所有上级分类
SELECT
p.category_id,
p.category_name,
p.parent_id,
ct.level + 1
FROM
categories p
JOIN
category_tree ct ON p.category_id = ct.parent_id
)
SELECT * FROM category_tree ORDER BY level;
如果分类有扩展属性:
SELECT
p.product_id,
p.product_name,
c.category_id,
c.category_name,
ca.attribute_name,
ca.attribute_value
FROM
products p
JOIN
product_categories pc ON p.product_id = pc.product_id
JOIN
categories c ON pc.category_id = c.category_id
LEFT JOIN
category_attributes ca ON c.category_id = ca.category_id
WHERE
p.product_id = 123;
您需要根据具体的数据库架构选择或调整上述查询方案。