Python实现列表内SQL表达式字符串子串级对比的问题求解

• 现有实现直接使用, 作为分隔符切割字符串，没有考虑concat()、case when这类SQL表达式内部本身就包含逗号的场景，会把完整的单个选择项拆成多个无效片段，后续集合计算自然不符合预期。
• 现有实现只计算了后一个字符串比前一个多的项，没有按照预期合并两个字符串的所有去重后的完整选择项。

通过状态机逻辑实现正确的SQL选择项拆分，拆分时忽略括号、特殊语句块内部的逗号，再进行去重合并即可：

def split_sql_select_items(s):
    items = []
    current = []
    # 状态标记：括号嵌套层数、是否在case语句块中
    bracket_level = 0
    in_case = 0
    for char in s:
        if char == '(':
            bracket_level += 1
        elif char == ')':
            bracket_level -= 1
        elif ''.join(current[-3:]).lower() == 'case' and bracket_level == 0:
            in_case = 1
        elif ''.join(current[-2:]).lower() == 'end' and bracket_level == 0 and in_case:
            in_case = 0
        # 只有不在括号、case块内的逗号才是分割符
        elif char == ',' and bracket_level == 0 and in_case == 0:
            items.append(''.join(current).strip())
            current = []
            continue
        current.append(char)
    # 加入最后一个项
    if current:
        items.append(''.join(current).strip())
    return items

lis = [
'concat(tb1.col1, tb1.col2, tb1.col3) as alias1, tb1.col4, case when tb2.col2>1 then 1 else 0 end as alias3', 
'concat(tb1.col1, tb1.col2, tb1.col3) as alias1, concat(tb2.col1, tb2.col2, tb2.col3) as alias2, case when tb1.col1>1 then 1 else 0 end as alias4, tb3.col1'
]

# 拆分所有项再去重，保留首次出现的顺序
seen = set()
result = []
for s in lis:
    items = split_sql_select_items(s)
    for item in items:
        if item not in seen:
            seen.add(item)
            result.append(item)

print(result)

['concat(tb1.col1, tb1.col2, tb1.col3) as alias1', 'tb1.col4', 'case when tb2.col2>1 then 1 else 0 end as alias3', 'concat(tb2.col1, tb2.col2, tb2.col3) as alias2', 'case when tb1.col1>1 then 1 else 0 end as alias4', 'tb3.col1']

和预期输出完全一致。

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