多对多关联与可变空值数据集的存储过程查询改写求助

数据库表结构 #

• Pages：存储搜索页面的搜索选项，结构为 pk_id, title, country, location
• Pages_Types：存储每个页面的0个或多个房产类型，结构为 fk_id, listing_type
• Listings：存储房产列表信息，结构为 pk_ref, title, country, location
• Listings_Types：存储每个房产列表的1个或多个类型，结构为 fk_ref, listing_type

业务需求 #

网页传入Pages.pk_id至存储过程，需返回匹配该页面搜索条件的Listings数据。搜索条件存在多种组合：

• 可能仅指定country，不指定location
• 可能同时指定country和location
• 部分页面关联了listing_type，部分没有

匹配规则 #

1. 若页面关联了listing_type，仅返回包含该类型且符合地域条件的房产列表
2. 若页面未关联listing_type，返回所有符合地域条件的房产列表

预期结果 #

• Page A应返回Listing 1，不返回Listing 4
• Page B应返回Listing 2和Listing 3
• Page C应返回Listing 2

正确查询方案 #

以下SQL语句适配多对多关联及空值条件，可直接用于存储过程：

SELECT DISTINCT l.*
FROM Pages p
LEFT JOIN Pages_Types pt ON p.pk_id = pt.fk_id
JOIN Listings l 
  ON (p.country = l.country)
  AND (p.location IS NULL OR p.location = l.location)
LEFT JOIN Listings_Types lt ON l.pk_ref = lt.fk_ref
WHERE 
  -- 页面有指定类型时，列表需包含至少一个对应类型
  (pt.listing_type IS NOT NULL AND lt.listing_type = pt.listing_type)
  -- 页面无指定类型时，直接匹配地域条件
  OR (pt.listing_type IS NULL)

逻辑说明 #

1. 地域条件兼容空值：通过p.location IS NULL OR p.location = l.location处理仅指定country的场景，确保空值条件下的匹配逻辑生效
2. 多对多类型匹配：用LEFT JOIN关联页面与列表的类型表，通过OR分支分别处理页面有/无类型的两种情况
3. 去重处理：多对多关联会产生重复行，DISTINCT保证返回的房产列表唯一

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