子查询一行按逗号分割为多行

需求：需要查询所有子查询结果集里面以英文逗号分割字符串的信息。

话不多说直接上最终截图。

图1子查询结果集

图2最终结果

解决方法：

一、首当其冲想到的是既然查询出来的结果集是以逗号分割的，而且符合SQL语法，那就直接执行一下。

1. 查询语句

SELECT 
*
FROM (SELECT DISTINCT
equ_devb.id AS devb_id,
equ_cert.id AS cert_id,
equ_devb.device_name AS '标准器名称',
CONCAT(equ_devb.model,'/',equ_devb.serial_no) AS '型号编号',
equ_devb.measure AS '测量范围',
equ_cert.remark AS '技术特征',
equ_cert.customer_name AS '溯源单位',
CONCAT(equ_cert.certificate_no,'/',DATE_FORMAT(ADDDATE((equ_cert.deadline),INTERVAL -1 DAY),'%Y.%m.%d')) AS '证书编号'


FROM lims_equipment.certificate equ_cert INNER JOIN  lims_equipment.device_base equ_devb
ON equ_devb.ID=equ_cert.device_id 
WHERE equ_devb.ID IN (

	(SELECT col_value FROM sample_std.jexcel_record_data WHERE sample_std.jexcel_record_data.record_id=
							(
																SELECT tr.EXCEL_RECORD_ID FROM lims_sample_test.test_record  tr WHERE ID IN(
																SELECT si.TEST_RECORD_ID
																FROM lims_sample_test.sample s RIGHT JOIN lims_sample_test.sample_item si
																ON s.ID=si.SAMPLE_ID
																WHERE s.ID= 1443627640568167941 )
							)))
ORDER BY equ_cert.id DESC)new_table
 GROUP BY new_table.devb_id

        2.查询截图 

图3直接查询结果

结果令人大失所望，经同事点拨，由于没有使用任何连接操作符，所有只执行了子查询的第一个字符串，因此，最终得到的结果并不是我们期望的。

二、既然没有连接，那就创造一个连接的条件，借助mysql.help_topic创建序列

1. 查询语句

SELECT 
*
FROM (SELECT DISTINCT
equ_devb.id AS devb_id,
equ_cert.id AS cert_id,
equ_devb.device_name AS '标准器名称',
CONCAT(equ_devb.model,'/',equ_devb.serial_no) AS '型号编号',
equ_devb.measure AS '测量范围',
equ_cert.remark AS '技术特征',
equ_cert.customer_name AS '溯源单位',
CONCAT(equ_cert.certificate_no,'/',DATE_FORMAT(ADDDATE((equ_cert.deadline),INTERVAL -1 DAY),'%Y.%m.%d')) AS '证书编号'


FROM lims_equipment.certificate equ_cert INNER JOIN  lims_equipment.device_base equ_devb
ON equ_devb.ID=equ_cert.device_id 
WHERE equ_devb.ID IN (
SELECT
	substring_index( substring_index( product.col_value, ',', topic.help_topic_id + 1 ), ',',- 1 ) AS tag 
FROM
	(SELECT col_value FROM sample_std.jexcel_record_data WHERE sample_std.jexcel_record_data.record_id=
							(
																SELECT tr.EXCEL_RECORD_ID FROM lims_sample_test.test_record  tr WHERE ID IN(
																SELECT si.TEST_RECORD_ID
																FROM lims_sample_test.sample s RIGHT JOIN lims_sample_test.sample_item si
																ON s.ID=si.SAMPLE_ID
																WHERE s.ID= 1443627640568167941 )
							)) product
	JOIN mysql.help_topic topic ON topic.help_topic_id < ( length( product.col_value ) - length( REPLACE ( product.col_value, ',', '' ) ) + 1 )
) 

 ORDER BY equ_cert.id DESC)  new_table
 GROUP BY new_table.devb_id

        2.直接上查询结果 

图4借助mysql.help_topic

欣喜若狂之际，又一个问题出现了，把SQL语句保存到我们自己系统的时候一直出问题，后来发现是我们系统里面的查询不能跨库的原因。这个解决办法应该是在我找到的解决办法里面最合适的。

三、既然不让借助库的序列，那我就自定义一个序列。

1. 查询语句

SELECT 
*
FROM (SELECT DISTINCT
equ_devb.id AS devb_id,
equ_cert.id AS cert_id,
equ_devb.device_name AS '标准器名称',
CONCAT(equ_devb.model,'/',equ_devb.serial_no) AS '型号编号',
equ_devb.measure AS '测量范围',
equ_cert.remark AS '技术特征',
equ_cert.customer_name AS '溯源单位',
CONCAT(equ_cert.certificate_no,'/',DATE_FORMAT(ADDDATE((equ_cert.deadline),INTERVAL -1 DAY),'%Y.%m.%d')) AS '证书编号'
FROM lims_equipment.certificate equ_cert INNER JOIN  lims_equipment.device_base equ_devb ON equ_devb.ID=equ_cert.device_id 
WHERE equ_devb.ID IN (
SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(new_table1.col_value, ',',n), ',',-1 ) AS tag 
FROM (SELECT col_value FROM sample_std.jexcel_record_data WHERE 
sample_std.jexcel_record_data.record_id=
								(
								SELECT tr.EXCEL_RECORD_ID FROM lims_sample_test.test_record  tr WHERE ID IN(
								SELECT si.TEST_RECORD_ID
								FROM lims_sample_test.sample s RIGHT JOIN lims_sample_test.sample_item si ON s.ID=si.SAMPLE_ID
								WHERE s.ID= 1443627640568167941 )
								)) as new_table1,(SELECT @rownum:=@rownum+1 as n FROM (SELECT @rownum:=0) r,(SELECT col_value FROM sample_std.jexcel_record_data WHERE 
sample_std.jexcel_record_data.record_id=
								(
								SELECT tr.EXCEL_RECORD_ID FROM lims_sample_test.test_record  tr WHERE ID IN(
								SELECT si.TEST_RECORD_ID
								FROM lims_sample_test.sample s RIGHT JOIN lims_sample_test.sample_item si ON s.ID=si.SAMPLE_ID
								WHERE s.ID= 1443627640568167941 )
								)) as new_table2) x 
WHERE n<(length(new_table1.col_value) - length( REPLACE (new_table1.col_value, ',', '' ) ) + 1 )
)
ORDER BY equ_cert.id DESC)  AS new_table
GROUP BY new_table.devb_id

    2.上查询截图

图5自定义序列结果

绕了一圈，又回到了原点，放弃这条道路。后来把语句拆分发现了其中的问题，就是数据的记录数量必须要大于逗号（最多一条记录）数量加1的数量。拿我这个查询来举例，就是我这条记录有三个id，但是记录只有一条，所以查询出来的还是一条。

四、自己创建多个连接

1. 查询语句

SELECT 
*
FROM (SELECT DISTINCT
equ_devb.id AS devb_id,
equ_cert.id AS cert_id,
equ_devb.device_name AS '标准器名称',
CONCAT(equ_devb.model,'/',equ_devb.serial_no) AS '型号编号',
equ_devb.measure AS '测量范围',
equ_cert.remark AS '技术特征',
equ_cert.customer_name AS '溯源单位',
CONCAT(equ_cert.certificate_no,'/',DATE_FORMAT(ADDDATE((equ_cert.deadline),INTERVAL -1 DAY),'%Y.%m.%d')) AS '证书编号'
FROM lims_equipment.certificate equ_cert INNER JOIN  lims_equipment.device_base equ_devb ON equ_devb.ID=equ_cert.device_id 
WHERE equ_devb.ID IN (
	SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(new_table1.col_value, ',',numbers.n), ',',-1 ) AS tag 
FROM (SELECT col_value FROM sample_std.jexcel_record_data WHERE 
sample_std.jexcel_record_data.record_id=
								(
								SELECT tr.EXCEL_RECORD_ID FROM lims_sample_test.test_record  tr WHERE ID IN(
								SELECT si.TEST_RECORD_ID
								FROM lims_sample_test.sample s RIGHT JOIN lims_sample_test.sample_item si ON s.ID=si.SAMPLE_ID
								WHERE s.ID= 1443627640568167941 )
								)) as new_table1
	INNER JOIN
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
   SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
   SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
   SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL
   SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbers
  ON CHAR_LENGTH(new_table1.col_value) - CHAR_LENGTH(REPLACE(new_table1.col_value, ',', '')) >= numbers.n - 1
)
ORDER BY equ_cert.id DESC)  AS new_table
GROUP BY new_table.devb_id

        2.直接上截图 

图6多个连接的查询

        先把查询语句保存到我们系统，弹出“保存成功”，喜出望外，非常感谢同事的大力支持。这个有点瑕疵，就是子查询结果集里面不能超过40个数量，也就是39个逗号。目前先这样用，大家如果有好的解决办法，请不吝赐教，随时更新。