SQL-一道特殊的字符串分解题目

本题不是一道直接的字符串拆解，

应用场景如下，表中有一个字段，是表示事件受影响的国家集合，使用逗号进行分隔，不幸的是，居然发现有些国家本身就带有逗号，这样在规范化的时候，如何准确地找到这些国家呢？

以下的代码是有一定限制的。但基本上够用。

下面的代码使用到了分析函数lag和lead还有cte,sqlserver2012及其以后的版本都支持，oracle好像10g以上就支持了。

主要思路：

字符串的分解，可以使用数字辅助表，然后cross join刷副本，然后根本分隔符出现的位置然后切豁字符串拆解到我们需要的东东。（解决方案中我使用的递归CTE来处理找到对应的位置）

现在还需要多加一步，就是对拆解的部分进行验证和去重不符合要求的那一部。

使用LAG和LEAD的好处，就是不需要再用自连接去找到对应的下一条数据了。

本题的解题原则是如何长项能连接到正确的国家，则取长项的，否则取短项的。

代码如下：

 --准备示例表与数据

drop table my_countries;

drop table valid_country;

   

create table my_countries(rid int,country_name_cc varchar(200));

insert into my_countries(rid,country_name_cc) values(1,'china,test, public of');

insert into my_countries(rid,country_name_cc) values(2,'us, public of,china,Evan, public of');

   

create table valid_country(cid int, country_name varchar(30));

insert into valid_country(cid,country_name) values(1,'china');

insert into valid_country(cid,country_name) values(2,'test, public of');

insert into valid_country(cid,country_name) values(3,'Evan, public of');

insert into valid_country(cid,country_name) values(4,'us, public of');

insert into valid_country(cid,country_name) values(5,'Evan');

--select * from my_countries;

--select * from valid_country;

 

正确的结果是：

WITH SPLIT_COUNTRY AS

(

SELECT

RID,

1 AS LVL,

1 AS STARTPOS,

CHARINDEX(',',COUNTRY_NAME_CC+',')-1 AS ENDPOS

FROM MY_COUNTRIES

UNION ALL

SELECT

SC.RID,

LVL+1 AS LVL,

ENDPOS+2,

CHARINDEX(',',COUNTRY_NAME_CC+',',ENDPOS+2)-1

FROM

MY_COUNTRIES CC JOIN

SPLIT_COUNTRY SC ON CC.RID=SC.RID

WHERE CHARINDEX(',',CC.COUNTRY_NAME_CC+',',ENDPOS+2)>0

)

,CTE_COUNTRY AS (

SELECT RID,LVL,STARTPOS,ENDPOS,LEAD(ENDPOS,1) OVER(PARTITION BY RID ORDER BY LVL) AS NEXTENDPOS FROM SPLIT_COUNTRY

)

,CTE AS (

SELECT MC.RID,SC.LVL,

CASE WHEN NEXTENDPOS IS NOT NULL AND EXISTS (SELECT * FROM VALID_COUNTRY VC WHERE VC.COUNTRY_NAME = SUBSTRING(COUNTRY_NAME_CC,STARTPOS,NEXTENDPOS-STARTPOS+1)) THEN

SUBSTRING(COUNTRY_NAME_CC,STARTPOS,NEXTENDPOS-STARTPOS+1)

ELSE

SUBSTRING(MC.COUNTRY_NAME_CC,STARTPOS,ENDPOS-STARTPOS+1)

END

AS COUNTRY

FROM MY_COUNTRIES MC JOIN CTE_COUNTRY SC

ON MC.RID=SC.RID

)

,CHECK_VALID AS (

SELECT CASE WHEN CHARINDEX(',',LAG(COUNTRY,1) OVER(PARTITION BY RID ORDER BY LVL))>0 THEN 0 ELSE 1 END AS ISVALID,

* FROM CTE

)

SELECT CV.RID,CV.COUNTRY,VC.CID FROM CHECK_VALID CV JOIN VALID_COUNTRY VC

ON CV.COUNTRY = VC.COUNTRY_NAME

AND ISVALID=1 ORDER BY RID;

 

 另一种方案，在第一种的基础上稍加修改：

WITH SPLIT_COUNTRY AS

(

SELECT

RID,

1 AS LVL,

1 AS STARTPOS,

CHARINDEX(',',COUNTRY_NAME_CC+',')-1 AS ENDPOS

FROM MY_COUNTRIES

UNION ALL

SELECT

SC.RID,

LVL+1 AS LVL,

ENDPOS+2,

CHARINDEX(',',COUNTRY_NAME_CC+',',ENDPOS+2)-1

FROM

MY_COUNTRIES CC JOIN

SPLIT_COUNTRY SC ON CC.RID=SC.RID

WHERE CHARINDEX(',',CC.COUNTRY_NAME_CC+',',ENDPOS+2)>0

)

,CTE_COUNTRY AS (

SELECT RID,LVL,STARTPOS,ENDPOS,LEAD(ENDPOS,1) OVER(PARTITION BY RID ORDER BY LVL) AS NEXTENDPOS FROM SPLIT_COUNTRY

)

,CTE AS (

SELECT MC.RID,SC.LVL,

SUBSTRING(MC.COUNTRY_NAME_CC,STARTPOS,ENDPOS-STARTPOS+1) AS COUNTRY,

SUBSTRING(COUNTRY_NAME_CC,STARTPOS,NEXTENDPOS-STARTPOS+1) AS COUNTRY2

FROM MY_COUNTRIES MC JOIN CTE_COUNTRY SC

ON MC.RID=SC.RID

)

SELECT CTE.RID,VC.COUNTRY_NAME,VC.CID

FROM

CTE JOIN VALID_COUNTRY VC

ON (CASE WHEN EXISTS(SELECT * FROM VALID_COUNTRY X WHERE X.COUNTRY_NAME=CTE.COUNTRY2) THEN CTE.COUNTRY2

ELSE CTE.COUNTRY END) = VC.COUNTRY_NAME

;