SQL如何找到特殊的电话号码?(substr、删表)

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

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

【面试题】

有一张“电话费用表”,包含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.考察删表、建表、从表中删除数据等技能的掌握程度

### SQL `SUBSTR` 函数使用说明 #### 基本语法 `SUBSTR` 是 SQL 中的一个重要函数,主要用于从字符串中提取子串。其基本语法如下: ```sql SUBSTR(string, position [, length]) ``` - `string`: 要从中提取子串的源字符串。 - `position`: 子串开始的位置。注意,在 SQL 中,字符串的第一个字符位置是 1 而不是 0[^2]。 - `length`: 可选参数,示要提取的字符数量。如果不提供此参数,则会从指定位置一直提取到字符串末尾。 #### 实际应用案例 当需要从数据库中的某一列获取特定部分的数据时,`SUBSTR` 就显得尤为有用。例如,假设有一个名为 `employees` 的格,其中有一列示员工全名 (`full_name`) ,现在想要查询每位员工名字的前三个字母: ```sql SELECT SUBSTR(full_name, 1, 3) AS first_three_chars FROM employees; ``` 这段代码将会返回一个新的列 `first_three_chars`,里面包含了每个员工姓名最前面的三个字符[^1]。 对于更复杂的情况,比如希望基于某些条件进行匹配操作,也可以利用 `SUBSTR` 来实现。比如说,如果只想找到那些姓氏以 "Li" 开头的人,可以通过以下方式构建查询语句: ```sql SELECT * FROM employees WHERE SUBSTR(last_name, 1, 2) = 'Li'; ``` 这将筛选出所有满足给定条件——即姓氏开头两个字母为 “Li”的记录。 另外需要注意的是,当输入字符串的实际长度小于所设定起始位置时,`SUBSTR` 返回的结果将是 NULL[^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值