基于独立表序列在SQL中获取任意字符串范围的实现方法

给定数据集 #

表A

表B

需求回顾 #

针对表A中TicketId=1的每一行：

• 正常情况：根据表B的Sequence字段，返回StartAnimal到EndAnimal（包含两端）的所有动物
• 边缘情况：如果EndAnimal不存在于表B中，仅返回该行的StartAnimal

解决方案SQL #

SELECT DISTINCT b.Animal
FROM TableA a
LEFT JOIN TableB s ON a.StartAnimal = s.Animal
LEFT JOIN TableB e ON a.EndAnimal = e.Animal
JOIN TableB b 
  ON b.Sequence BETWEEN s.Sequence 
                    AND COALESCE(e.Sequence, s.Sequence)
WHERE a.TicketId = 1
ORDER BY b.Sequence;

逻辑解释 #

1. 关联序列值：通过两次LEFT JOIN分别把表A的StartAnimal和EndAnimal与表B关联，拿到对应的起始序列s.Sequence和结束序列e.Sequence——用LEFT JOIN是为了兼容EndAnimal不存在的场景，此时e.Sequence会是NULL。
2. 处理边缘场景：用COALESCE(e.Sequence, s.Sequence)判断，如果结束序列不存在，就把范围收缩成起始序列到自身，这样就只会返回StartAnimal。
3. 筛选范围内的动物：再关联表B，筛选出序列落在上述范围内的所有动物，用DISTINCT避免重复结果（比如如果有重复的动物条目）。
4. 排序输出：最后按Sequence排序，保证结果顺序符合序列逻辑。

边缘场景验证 #

假设表B中没有Bee，那么e.Sequence为NULL，COALESCE会返回s.Sequence（即Monkey的1），此时第一行只会返回Monkey；第二行的EndAnimal存在，正常返回Lion、Buffalo，最终结果就是Monkey、Lion、Buffalo，完全符合需求。

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