如何在SQL中查询多级分类关联的商品？（CodeIgniter+MySQL电商场景）

没问题，针对你这套三级分类+商品的数据库结构，我整理了几种常用的SQL查询场景，应该能覆盖你电商应用里的大部分需求：

如果需要展示商品时同时带出它所属的一级、二级、三级分类名称，用这个查询：

SELECT 
    p.product_id,
    p.product_name,
    c.category_name AS top_category,
    sc.subcategory_name AS second_category,
    ssc.subsubcategory_name AS third_category
FROM product p
INNER JOIN subsubcategory ssc ON p.product_subsubcategory_id = ssc.subsubcategory_id
INNER JOIN subcategory sc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
INNER JOIN category c ON sc.subcategory_category_id = c.category_id;

说明：这里用INNER JOIN是假设所有商品都必须关联到一个三级分类；如果你的业务允许商品暂时不归属任何分类，可以把INNER JOIN换成LEFT JOIN，这样无分类的商品也会被查出来，分类字段会显示为NULL。

如果用户点击了某个一级分类，要查询该分类下所有层级的商品：

SELECT 
    p.product_id,
    p.product_name,
    sc.subcategory_name,
    ssc.subsubcategory_name
FROM product p
INNER JOIN subsubcategory ssc ON p.product_subsubcategory_id = ssc.subsubcategory_id
INNER JOIN subcategory sc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
INNER JOIN category c ON sc.subcategory_category_id = c.category_id
WHERE c.category_id = 1; -- 替换成你要查询的顶级分类ID

针对二级分类的商品筛选：

SELECT 
    p.product_id,
    p.product_name,
    ssc.subsubcategory_name
FROM product p
INNER JOIN subsubcategory ssc ON p.product_subsubcategory_id = ssc.subsubcategory_id
INNER JOIN subcategory sc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
WHERE sc.subcategory_id = 5; -- 替换成目标二级分类ID

最直接的三级分类查询，因为商品表直接关联了三级分类ID：

SELECT 
    product_id,
    product_name
FROM product
WHERE product_subsubcategory_id = 20; -- 替换成目标三级分类ID

如果需要同时带出上级分类信息，也可以加上JOIN：

SELECT 
    p.product_id,
    p.product_name,
    sc.subcategory_name,
    c.category_name
FROM product p
INNER JOIN subsubcategory ssc ON p.product_subsubcategory_id = ssc.subsubcategory_id
INNER JOIN subcategory sc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
INNER JOIN category c ON sc.subcategory_category_id = c.category_id
WHERE ssc.subsubcategory_id = 20;

查看某一件商品所属的全部分类层级：

SELECT 
    c.category_name AS top_category,
    sc.subcategory_name AS second_category,
    ssc.subsubcategory_name AS third_category,
    p.product_name
FROM product p
INNER JOIN subsubcategory ssc ON p.product_subsubcategory_id = ssc.subsubcategory_id
INNER JOIN subcategory sc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
INNER JOIN category c ON sc.subcategory_category_id = c.category_id
WHERE p.product_id = 100; -- 替换成目标商品ID

这些查询应该能满足你电商应用里商品列表展示、分类筛选、商品详情页展示分类信息的需求。如果有特定的复杂查询场景（比如分页、排序、多条件筛选），可以再补充细节~

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