如何在单条SQL查询中同时实现城市ID的包含与排除逻辑？

背景说明 #

MainAccountID是树状结构的根节点，每个根节点下可包含多个Account节点，每个Account节点下可包含多个City节点，每个子City节点下还可包含多个城市。

数据表结构与数据 #

ConfigFlow.tb（流程配置表） #

每条记录代表特定的流程信息，需通过包含和排除城市ID来生成：

MainAccountID,IncludeMainCityId,ExcludeMainCityId
1100,100,200
1200,200,300

AccountIds.tb（账户关联表） #

AccountId,MainAccountID
1,1100
2,1100
3,1200

CityIds.tb（城市关联表） #

CityId,MainCityId 
11,100
12,100
13,200
14,300

当前实现与需求 #

目前已通过内联ConfigFlow.tb与AccountIds.tb（关联字段为MainAccountID）获取所有子账户节点，再内联CityIds.tb得到IncludeMainCityId下的所有城市ID，输出结果如下：

仅包含IncludeMainCityId的输出
1100,100,200,   1,1100,     11,100
1100,100,200,   2,1100,     12,100
1200,200,300,   3,1200,     13,200

现需要编写SQL查询，同时输出IncludeMainCityId和ExcludeMainCityId对应的城市ID，期望输出格式如下：

{ConfigFlow.tb}     {AccountIds.tb}     {IncludeCityIds}    {ExcludeCityIds}
1100,100,200,       1,1100,             11,100               0,0
1100,100,200,       2,1100,             12,100               0,0
1200,200,300,       3,1200,             13,200               0,0
1100,100,200,       1,1100,             0,0                  13,200
1200,200,300,       3,1200,             0,0,                 14,300 

注：未列出所有列名，但每个逗号分隔项对应对应表的列。

解决方案 #

可以通过UNION ALL将包含城市的结果集与排除城市的结果集合并，同时关联城市表并处理空值为0,0来实现需求：

-- 输出包含的城市记录，排除城市字段填0,0
SELECT 
    cf.MainAccountID,
    cf.IncludeMainCityId,
    cf.ExcludeMainCityId,
    ai.AccountId,
    ai.MainAccountID AS Account_MainAccountID,
    ci.CityId AS IncludeCityId,
    ci.MainCityId AS IncludeMainCityId_City,
    '0' AS ExcludeCityId,
    '0' AS ExcludeMainCityId_City
FROM ConfigFlow.tb cf
JOIN AccountIds.tb ai ON cf.MainAccountID = ai.MainAccountID
JOIN CityIds.tb ci ON cf.IncludeMainCityId = ci.MainCityId

UNION ALL

-- 输出排除的城市记录，包含城市字段填0,0
SELECT 
    cf.MainAccountID,
    cf.IncludeMainCityId,
    cf.ExcludeMainCityId,
    ai.AccountId,
    ai.MainAccountID AS Account_MainAccountID,
    '0' AS IncludeCityId,
    '0' AS IncludeMainCityId_City,
    ce.CityId AS ExcludeCityId,
    ce.MainCityId AS ExcludeMainCityId_City
FROM ConfigFlow.tb cf
JOIN AccountIds.tb ai ON cf.MainAccountID = ai.MainAccountID
JOIN CityIds.tb ce ON cf.ExcludeMainCityId = ce.MainCityId

说明 #

1. 第一部分查询关联IncludeMainCityId对应的城市，将排除城市的字段设为0；
2. 第二部分查询关联ExcludeMainCityId对应的城市，将包含城市的字段设为0；
3. 使用UNION ALL合并两个结果集，即可得到匹配期望格式的输出。

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