sql查询中将表中的记录按照某个字段中的分割符分成多行

场景，一个积分收入记录表里面有个字段记录多个参与活动的i规则id,主要字段如下：

业务要求根据income_rule_id去关联规则表获取规则信息并更新到积分变更表中，牵扯到的具体表和逻辑不再一一展示，在这个过程中需要把表中的每一条记录根据ncome_rule_id的分隔符分割成多条记录表结构不变以方便做连接查询。废话不多说直接上sql，和结果：

sqlserver:

SELECT
	a.activity_income_rule_id,
	a.activity_id,
	a.active_flag,
	b.value
	income_rule_id 
FROM
	(
SELECT
	c.activity_income_rule_id,
	c.activity_income_rule_name,
	c.activity_id,
	c.active_flag,
	c.company_code,
	income_rule_id = CONVERT ( XML, '<root><v>' + REPLACE ( income_rule_id, ',', '</v><v>' ) + '</v></root>' ) 
FROM
	CRM_CreditActivityIncomeRule c WITH ( NOLOCK ) 
	) a OUTER APPLY (
SELECT 
value
	= N.v.
value
	( '.', 'varchar(100)' ) 
FROM
	a.income_rule_id.nodes ( '/root/v' ) N ( v ) 
	) b

结果如下：

mysql:

SELECT
	a.activity_income_rule_id,
	a.activity_id,
	a.active_flag,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.`income_rule_id`, ',', b.help_topic_id + 1 ), ',', - 1 ) AS income_rule_id 
FROM
	CRM_CreditActivityIncomeRule a
	INNER JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`income_rule_id` ) - LENGTH( REPLACE ( a.`income_rule_id`, ',', '' ) ) + 1 )

结果：

经过上述处理可以方便后续进行连接查询！

建议：直接粘贴代码套公式即可！

公式总结如下：

SQL server：

SELECT
   a.其他字段,
   a.其他字段,
   ...,
   b.value 别名
FROM
   (
SELECT
   c.其他字段,
   c.其他字段,
   ...,
   需要被分割的字段 = CONVERT ( XML, '<root><v>' + REPLACE ( 需要被分割的字段, ',', '</v><v>' ) + '</v></root>' )
FROM
   表名 c WITH ( NOLOCK )
   ) a OUTER APPLY (
SELECT
value
   = N.v.
value
   ( '.', 'varchar(100)' )
FROM
   a.需要被分割的字段.nodes ( '/root/v' ) N ( v )
   ) b
MySQL：

SELECT
   a.其他字段,
   a.其他字段,
   ...,
   SUBSTRING_INDEX( SUBSTRING_INDEX( a.`需要被分割的字段`, ',', b.help_topic_id + 1 ), ',', - 1 ) AS 别名
FROM
   表名 a
   INNER JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`需要被分割的字段` ) - LENGTH( REPLACE ( a.`需要被分割的字段`, ',', '' ) ) + 1 )