mysql 把一段数据变成一个临时表-查询没有的数据也显示

例子：有一组手机号，查询mysql库里有的是会员，没有的标记为非会员，按序号顺序显示

1. 先把一组手机号作为一个字段

SELECT '15555555555,12222222222' AS phone FROM DUAL 

效果：

2.把这个字段分割成一列数据

SELECT
	substring_index( 
        substring_index( a.phone, ',', b.help_topic_id + 1 ),
         ',', - 1 ) AS phone 
FROM
	( SELECT '15555555555,12222222222' AS phone FROM DUAL ) a
	INNER JOIN mysql.help_topic b ON b.help_topic_id < (
		length( a.phone ) - length(
		REPLACE ( a.phone, ',', '' )) + 1 
	)

效果：

 

 3. 关联对应的mysql数据库其他表，把字段对应好，查出来

SELECT
	aaa.phone '会员手机号',
IF
	( ISNULL( mmm.phone ), '非会员', '会员' ) '是否会员' 
FROM
	(
	SELECT
		substring_index( 
            substring_index( a.phone, ',', b.help_topic_id + 1 ),
             ',', - 1 ) AS phone 
	FROM
		( SELECT '15555555555,12222222222' AS phone FROM DUAL ) a
	INNER JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.phone ) - length( REPLACE ( a.phone, ',', '' )) + 1 )) aaa
	LEFT JOIN member mmm ON aaa.phone = mmm.phone

效果：

 

4.自带排序的序号

SELECT
  @1 := @1+1 AS '序号', 
	aaa.phone '会员手机号',
IF
	( ISNULL( mmm.phone ), '非会员', '会员' ) '是否会员' 
FROM
	(
	SELECT
		substring_index( 
            substring_index( a.phone, ',', b.help_topic_id + 1 ),
             ',', - 1 ) AS phone 
	FROM
		( SELECT '15555555555,12222222222' AS phone FROM DUAL ) a
	INNER JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.phone ) - length( REPLACE ( a.phone, ',', '' )) + 1 )) aaa
	LEFT JOIN member mmm ON aaa.phone = mmm.phone,(SELECT @1 := 0 ) r

效果：

 

各位大神，如有不同意见欢迎下方留言