多表关联按产品-类型组合过滤数据的SQL查询问题

现有数据库表 #

1. product表：

2. 产品对应独立类型表：

• Product_A_Type表：

• Product_B_Type表：

3. stock表：

需求描述 #

按产品与类型的组合过滤数据：

• 搜索产品"A"且类型匹配%ab%时，仅返回对应Product_A_Type的匹配记录
• 搜索产品"B"且类型匹配%12%时，仅返回对应Product_B_Type的匹配记录

当前错误查询语句 #

当前编写的SQL返回空结果，原查询如下：

select 
  * 
from 
  daily_stock 
where 
  (
    product_id in (
      select 
        id 
      from 
        products p 
      where 
        lower(p.name) like lower('%lubr%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        lubricant_type l 
      where 
        lower(l.type) like lower('%%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        lubricant_type l 
      where 
        lower(l.name) like lower('%%')
    )
  ) 
  and (
    product_id in (
      select 
        id 
      from 
        products p 
      where 
        lower(p.name) like lower('%lubr%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        fuel_type f 
      where 
        lower(f.type) like lower('%%')
    ) 
    and product_type_id in (
      select 
        id 
      from 
        fuel_type f 
      where 
        lower(f.name) like lower('%%')
    )
  )

错误原因 #

1. 逻辑冲突：用AND同时要求product_type_id既存在于lubricant_type又存在于fuel_type，两个集合无交集，直接导致结果为空；
2. 表名不匹配：原查询使用的表名（如daily_stock、products）与实际数据库表名（stock、product、Product_A_Type等）不一致；
3. 条件冗余：重复校验product_type_id的无意义条件（like lower('%%')）。

修正后的查询方案 #

方案1：分场景UNION ALL查询（直观易维护） #

针对不同产品+类型的组合需求，拆分查询后合并结果：

-- 匹配产品"A" + 类型含"ab"的记录
SELECT s.*
FROM stock s
JOIN product p ON s.product_id = p.id
JOIN Product_A_Type t ON s.product_type_id = t.id
WHERE p.name = 'A' AND t.type LIKE '%ab%'

UNION ALL

-- 匹配产品"B" + 类型含"12"的记录
SELECT s.*
FROM stock s
JOIN product p ON s.product_id = p.id
JOIN Product_B_Type t ON s.product_type_id = t.id
WHERE p.name = 'B' AND t.type LIKE '%12%';

方案2：单语句条件分支查询（适配动态参数） #

如果需要通过参数动态切换搜索条件，可使用左关联+条件判断：

SELECT s.*
FROM stock s
JOIN product p ON s.product_id = p.id
LEFT JOIN Product_A_Type ta ON s.product_type_id = ta.id AND p.name = 'A'
LEFT JOIN Product_B_Type tb ON s.product_type_id = tb.id AND p.name = 'B'
WHERE 
  (p.name = 'A' AND ta.type LIKE '%ab%')
  OR (p.name = 'B' AND tb.type LIKE '%12%');

预期结果示例 #

• 搜索产品"A"且类型%ab%时，返回：

• 搜索产品"B"且类型%12%时，返回：

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