SQL如何找到特殊的电话号码？（substr、删表）

最新推荐文章于 2024-03-01 14:23:37 发布

Begin to change

最新推荐文章于 2024-03-01 14:23:37 发布

阅读量1.2k

点赞数 2

分类专栏： # SQL面试题文章标签： sql 数据库

SQL面试题专栏收录该内容

69 篇文章 17 订阅

订阅专栏

本文通过逻辑树分析方法解决两个SQL查询问题。首先，详细阐述如何查找2017年前10个月电话号码尾号符合特定模式的记录，涉及子查询、字符串截取及条件判断。接着，介绍了如何删除10月份的重复数据，包括查询重复记录及执行删除操作。内容覆盖了SQL中的子查询、分组汇总及数据清理技巧。

摘要由CSDN通过智能技术生成

【面试题】

有一张“电话费用表”，包含3个字段：电话号码（8位数）、月份、月消费。

其中，月消费为0表明该月没有产生费用。第一行数据含义：电话号码（64262631）在月份（2017年11月）产生的月消费（30.6元的话费）。

【问题一】查找2017年以来（截止到10月31日）所有四位尾数符合AABB或者ABAB或者AAAA的电话号码（A、B分别代表1-9中任意的一个数字）

【问题二】删除“电话费用表”中10月份出现的重复数据。

【解题步骤】

问题1：复杂查询

用逻辑树分析方法，把问题一拆解为下面的子问题，也就是我们要找到符合以下条件的电话号码：

1）条件一：电话费用表中201701至201710的电话号码；

2）条件二：电话号码四位尾数全部是1-9中任意的一个数字；

3）条件三：电话号码四位尾数符合AABB或ABAB或AAAA三种格式。

所以，先获取符合条件一的电话号码，同时分别取出电话号码的四位尾数，用于下一步判断。

这里会用到一个字符串截取的函数：substr()，用法如下：


select 电话号码,
       substr(电话号码, 5, 1) as 第5位数,
       substr(电话号码, 6, 1) as 第6位数,
       substr(电话号码, 7, 1) as 第7位数,
       substr(电话号码, 8, 1) as 第8位数
from 电话费用表
where 月份 >= 201701 and 月份 <= 201710;

运行SQL语句，获得查询结果（“中间结果一”）：

在“中间结果一”的基础上（也就是把上面查询结果作为子查询），进行条件二（电话号码四位尾数全部是1-9中任意的一个数字）


select distinct 电话号码
from
(
select 电话号码,
       substr(电话号码, 5, 1) as 第5位数,
       substr(电话号码, 6, 1) as 第6位数,
       substr(电话号码, 7, 1) as 第7位数,
       substr(电话号码, 8, 1) as 第8位数
from 电话费用表
where 月份 >= 201701 and 月份 <= 201710
) as t1
where (第5位数 >= 1 and 第5位数 <= 9)
and (第6位数 >= 1 and 第6位数 <= 9)
and (第7位数 >= 1 and 第7位数 <= 9)
and (第8位数 >= 1 and 第8位数 <= 9);

条件三的判断（电话号码四位尾数符合AABB或ABAB或AAAA三种格式），也就是AABB格式是第5位数 = 第6位数 and 第7位数 = 第8位数，ABAB格式是第5位数 = 第7位数 and 第6位数 = 第8位数，AAAA格式是第5、6、7、8位数一样，这种情况包括在了前面两种格式中。

把条件三的判断加入上面SQL中


 (第5位数 = 第6位数 and 第7位数 = 第8位数) or 
  (第5位数 = 第7位数 and 第6位数 = 第8位数)

最终SQL如下：


select distinct 电话号码
from
(
select 电话号码,
       substr(电话号码, 5, 1) as 第5位数,
       substr(电话号码, 6, 1) as 第6位数,
       substr(电话号码, 7, 1) as 第7位数,
       substr(电话号码, 8, 1) as 第8位数
from 电话费用表
where 月份 >= 201701 and 月份 <= 201710
) as t1
where (第5位数 >= 1 and 第5位数 <= 9)
and (第6位数 >= 1 and 第6位数 <= 9)
and (第7位数 >= 1 and 第7位数 <= 9)
and (第8位数 >= 1 and 第8位数 <= 9)
and (
      (第5位数 = 第6位数 and 第7位数 = 第8位数) or 
      (第5位数 = 第7位数 and 第6位数 = 第8位数)
     );

问题2：删除重复数据

【问题二】的删除重复值是数据清洗中常用的技能。

1.查询出重复数据

可以看之前写过的《如何查找重复数据？》，本案例查询重复数据SQL如下

select *
from
(
 select *,count(*) as countNumber
 from 电话费用表
 where 月份 = 201710
 group by  电话号码,月份,月消费
) as t
where countNumber > 1;

2.删除重复数据

删除数据用delete语句。


delete 
from 电话费用表 
where 电话号码 in (
select 电话号码
from
(
 select *,count(*) as countNumber
 from 电话费用表
 where 月份 = 201710
 group by  电话号码,月份,月消费
) as t
where countNumber > 1
);

【本题考点】

1.考查对子查询的掌握程度

2.考查对分组汇总的掌握程度

3.考察删表、建表、从表中删除数据等技能的掌握程度