最近写了一个东西有要求一个值需要筛选两个条件，其中一个条件的栏位值在数据库中形式不固定，有单个的，有复合的
（类似有的是a, b, c…有的是a/b, c/d, a/c/d）但是其筛选条件集合是这样的(a, b, c, d……)。
所以要对复合值另外处理，我的想法是以 / 为分隔符将复合值分开再进行筛选。

分享我的sql：

def L2proNum(departs_str):sql4 = "select count(uaer_no) from table_a where " \"depart in ('" + departs_str + "') and name like '%二次元%'"data4 = querySql(sql4)for r in data4:L2ProjectNum = r[0]# 将depart栏位中所有复合值挑出来sql = "select depart from table_a where depart like '%/%' and name like '%二次元%'"data = querySql(sql)cooperateDeparts = [r[0] for r in data]# 以‘/’为标记将这些复合值分离为单个的，并筛选其是否符合条件（ num in ('" + departs_str + "') ）for cooperateDepart in cooperateDeparts:sql = "select num from(SELECT SUBSTRING_INDEX(" \"SUBSTRING_INDEX('" + cooperateDepart + "','/',help_topic_id+1),'/',-1) " \"AS num FROM mysql.help_topic WHERE help_topic_id < " \"LENGTH('" + cooperateDepart + "')-LENGTH(REPLACE('" + cooperateDepart + "','/',''))+1)as q " \"where num in ('" + departs_str + "')"data = queryMysql(sql)projectDeparts = [r[0] for r in data]#只要projectDeparts不为空，就是有值符合条件，不论是多少个值符合条件，只算一个L2Projectif projectDeparts:L2ProjectNum = L2ProjectNum + 1return L2ProjectNum

那段以 / 为分隔符将复合值分离的sql我理解了半天：

"select num from(SELECT SUBSTRING_INDEX(" \
"SUBSTRING_INDEX('" + cooperateDepart + "','/',help_topic_id+1),'/',-1) " \
"AS num FROM mysql.help_topic WHERE help_topic_id < " \
"LENGTH('" + cooperateDepart + "')-LENGTH(REPLACE('" + cooperateDepart + "','/',''))+1)as q " 

大体就是
SUBSTRING_INDEX(‘" + cooperateDepart + "’,‘/’,help_topic_id+1) 把 ‘a, b, c, d’ 这个字符串正着截取为
‘a’
‘a,b’
‘a,b,c’
‘a, b, c, d’

SUBSTRING_INDEX(" “SUBSTRING_INDEX('” + cooperateDepart + "‘,’/‘,help_topic_id+1),’/',-1) 又挨个儿截取了上面那些字符串的倒数第一个就成了：
‘a’
‘b’
‘c’
‘d’

REPLACE(‘" + cooperateDepart + "’,‘/’,‘’) 将字符串中的 ‘/’ 替换为空

LENGTH(‘" + cooperateDepart + "’)-LENGTH(REPLACE(‘" + cooperateDepart + "’,‘/’,‘’))+1) 这句就是help_topic_id循环的次数（'/'在字符串中出现的次数 + 1 = 最终要分割为几个字符串）

具体解释，点这里跳转