SQL 怎么查询手机号只有三个不同的数字的两种方式

第一种：这种比较慢，因为嵌套了各种子查询

SELECT c1.phone_number, CASE WHEN c2.num <= 3 THEN '' ELSE '' END FROM customer_db.customer c1 LEFT JOIN (
SELECT customer.phone_number,COUNT(DISTINCT customer.phone) num FROM (
SELECT phone_number,SUBSTR(phone_number,1,1) phone FROM  customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,2,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,3,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,4,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,5,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,6,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,7,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,8,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,9,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,10,1) FROM customer UNION ALL 
SELECT phone_number,SUBSTR(phone_number,11,1) FROM customer) customer GROUP BY customer.phone_number) c2 ON c1.phone_number=c2.phone_number

第二种：这种比较快推荐大家使用

SELECT id, phone_number, 
  case when length(phone_number) = 11 -- 11位
    and phone_number REGEXP '^1[0123456789]{10}' -- 首位“1”，后10位都是数字
    and (if(length(replace(phone_number,0,''))<11,1,0)
      +if(length(replace(phone_number,1,''))<11,1,0)
      +if(length(replace(phone_number,2,''))<11,1,0)
      +if(length(replace(phone_number,3,''))<11,1,0)
      +if(length(replace(phone_number,4,''))<11,1,0)
      +if(length(replace(phone_number,5,''))<11,1,0)
      +if(length(replace(phone_number,6,''))<11,1,0)
      +if(length(replace(phone_number,7,''))<11,1,0)
      +if(length(replace(phone_number,8,''))<11,1,0)
      +if(length(replace(phone_number,9,''))<11,1,0))  >= 3 -- 至少3位不重复数字
    then '是手机号'
    else '不是手机号' end 
FROM phone  LIMIT 50 ;