SQL的一些常见案例(1)

最新推荐文章于 2024-09-13 17:04:58 发布

2401_83739411

最新推荐文章于 2024-09-13 17:04:58 发布

阅读量445

点赞数 22

分类专栏： 2024年程序员学习文章标签： sql 数据库

本文链接：https://blog.csdn.net/2401_83739411/article/details/137660620

版权

2024年程序员学习专栏收录该内容

288 篇文章 1 订阅

订阅专栏

select * from test1


![](https://img-blog.csdnimg.cn/33c0d16b212d4aff9b02747f07e621d8.png)


        是一个如下的表： 


![](https://img-blog.csdnimg.cn/448d598b35b542a1827cd82848472ab2.png)


### 方法一 ：开窗

select test1.*,sum(x) over(order by person_id ) from test1


![](https://img-blog.csdnimg.cn/d628cac4478545e891ea097e65193483.png)


        倒序排列：

select test1.*,sum(x) over(order by person_id desc) from test1


![](https://img-blog.csdnimg.cn/220b22126738458e8390a2bde92969e8.png)


        用这种方式可以实现**分组累计求和**


### 方法二：自连接


表a.                            表b.


 ![](https://img-blog.csdnimg.cn/f20c1315d12845258d1461802d4f0428.png)![](https://img-blog.csdnimg.cn/bdf34e4756564c10b15aadbaf4f88afc.png)


        在如 MySQL 不支持开窗函数时，可以用第二种方法——自连接，用表a控制求和的位置，用表b的x求和（只用一个表就统一都是截至某行的sum值了，必须要自连接）

select a.person_id,(
SELECT SUM (b.x) FROM test1 b WHERE b.person_id <= a.person_id )from test1 a


## 二、删除有重复的数据


        建表：

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (id, email) values (‘1’, ‘john@example.com’)
insert into Person (id, email) values (‘2’, ‘bob@example.com’)
insert into Person (id, email) values (‘3’, ‘john@example.com’)


        ID为唯一标识 


![](https://img-blog.csdnimg.cn/d20b34863c9d4685902fff9a25b9e995.png)


大致思路： 


        使用 **max() 或 min() 函数对其唯一标识处理，仅保留一条数据**，若无唯一标识 ORACLE 中可以使用 **ROWID**，筛选出需要唯一保留的数据，其他删除

DELETE FROM PERSON WHERE ID NOT IN
(SELECT MIN(ID) FROM PERSON GROUP BY EMAIL )


## 三、连续问题


        建表：

Create table Logs (id int, num int);
Truncate table Logs;
insert into Logs (id, num) values (‘1’, ‘1’);
insert into Logs (id, num) values (‘2’, ‘1’);
insert into Logs (id, num) values (‘3’, ‘1’);
insert into Logs (id, num) values (‘4’, ‘2’);
insert into Logs (id, num) values (‘5’, ‘1’);
insert into Logs (id, num) values (‘6’, ‘2’);
insert into Logs (id, num) values (‘7’, ‘2’);


        ID为唯一标识


### 方法一：位移函数:


        lead()/lag() over()，若至少重复三次，则其按**ID排序前一位和后一位均为相同数字**，如下：

SELECT LOGS.*,
lag(NUM,1) OVER(ORDER BY ID) as last,
lead(NUM,1) OVER(ORDER BY ID) as next
FROM LOGS


![](https://img-blog.csdnimg.cn/d7ca833edd994fd48c12b6c40d316661.png)


         再筛选出前后一位均和 num 相等的数据，进行去重，即可得到至少重复三次的数字

SELECT distinct num ConsecutiveNums FROM(
SELECT LOGS.*,
lag(NUM,1) OVER(ORDER BY ID) last,
lead(NUM,1) OVER(ORDER BY ID) next
FROM LOGS )
where num=last and num=next


![](https://img-blog.csdnimg.cn/a4ee4f51563946f9a41046c629e91721.png)


         位移函数还可以用来算**移动平均**哦


### 方法二：row\_num()构造新列


        这里的假设需要ID是连续的

select
Id,Num,row_number() over(partition by Num order by id) as RK,
id-row_number() over(partition by Num order by id) des
from Logs


![](https://img-blog.csdnimg.cn/933e459f30214d839c751268aba281c0.png)


        先看为何这样构造，注意到，使用开窗函数对 num 分组后，**由于 row\_num() 从 1 开始连续的，因此若 num 也是连续出现的，那每组内 ID - RK 之间的值将一直相等**，接下来在这个表里只需要筛选出差值出现次数大于等于3次即可

SELECT num,des,count(1) FROM (
select
Id,Num,row_number() over(partition by Num order by id) as RK,
id-row_number() over(partition by Num order by id) des
from Logs) group by num,des


![](https://img-blog.csdnimg.cn/3c49474e635647389fbe5d392c1aa6ea.jpeg)


        加上 count()>=3 的条件


**注意：若ID是从0开始的序列会有误差，此时将ID+1再作差即可~~**

SELECT num ConsecutiveNums FROM (
SELECT
Id,Num,row_number() over(partition by Num order by id) as RK,
id-row_number() over(partition by Num order by id) des
FROM Logs)
GROUP BY num,des HAVING count(1)>=3


![](https://img-blog.csdnimg.cn/9e4d41032ebe4630acb4dda5c6ea49fc.png)


        **这是解决连续问题比较通用的思想，如顾客连续购买日期、连续登录等等问题均可用该方法解决**


## 四、基于连接的两表比较


        两表做连接是 SQL 中最简单的处理之一，建表：

Create table A (X int);
Create table B (Y int);
Truncate table A;
Truncate table B;

insert into A (X) values (1);
insert into A (X) values (2);
insert into A (X) values (3);
insert into A (X) values (4);
insert into A (X) values (5);

insert into B (Y) values (1);
insert into B (Y) values (3);
insert into B (Y) values (5);
insert into B (Y) values (7);
insert into B (Y) values (9);

SELECT * FROM A;
SELECT * FROM B


![](https://img-blog.csdnimg.cn/a62f71ad5f99471a938e62e2a6d69999.png)![](https://img-blog.csdnimg.cn/1f90e08ce4a0499dade3d176b4cd8e62.png)


         左外连接

SELECT * FROM A left join b on a.x=b.y


![](https://img-blog.csdnimg.cn/d77589e8670745a981c004759878dcb0.png)


        右外连接

SELECT * FROM A right join b on a.x=b.y


![](https://img-blog.csdnimg.cn/82e3fc48e2aa495d81fd7f9abfd7c9ed.png)


        全外连接

SELECT * FROM A FULL JOIN b on A.X=B.Y


![](https://img-blog.csdnimg.cn/16736388862a4257a0238ab39eaa1923.png)


        实际上，简单外连接可以**检查仅**其**中一表有而另外一表没有的数据（表中含有空值的）**，如有无某个行为的用户，某商品有无购买记录，或模拟销售退款行为等等


## 五、分区间计数的思想


**使用 SUM(CASE WHEN)或COUNT(CASE WHEN)，先判断，后计数**


        假设现在有一表 A，id为主键，唯一，想看 X 落在哪个区间范围内






**自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。**

**深知大多数Linux运维工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！**

**因此收集整理了一份《2024年Linux运维全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。**
![img](https://img-blog.csdnimg.cn/img_convert/69170ac3be96c3b8723123483515da7f.png)
![img](https://img-blog.csdnimg.cn/img_convert/86ac2e3faf665ce41e6d658d1dad1f4f.png)
![img](https://img-blog.csdnimg.cn/img_convert/0cb717f0b14d5fbbd142f8ad675587aa.png)
![img](https://img-blog.csdnimg.cn/img_convert/acb30a2faf6cd3bc4d40dd594eed2c1d.png)
![img](https://img-blog.csdnimg.cn/img_convert/50356100e0d77e0e99ab020ad60d4b90.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Linux运维知识点，真正体系化！**

**由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新**

**如果你觉得这些内容对你有帮助，可以添加VX：vip1024b （备注Linux运维获取）**
![img](https://img-blog.csdnimg.cn/img_convert/0a2f85d48281ae0dd1faf1c0aa924e45.jpeg)



为了做好运维面试路上的助攻手，特整理了上百道 **【运维技术栈面试题集锦】** ，让你面试不慌心不跳，高薪offer怀里抱！

这次整理的面试题，**小到shell、MySQL，大到K8s等云原生技术栈，不仅适合运维新人入行面试需要，还适用于想提升进阶跳槽加薪的运维朋友。**

![](https://img-blog.csdnimg.cn/img_convert/2d3fe6412503df3b00d598ec892daa98.png)

本份面试集锦涵盖了

*   **174 道运维工程师面试题**
*   **128道k8s面试题**
*   **108道shell脚本面试题**
*   **200道Linux面试题**
*   **51道docker面试题**
*   **35道Jenkis面试题**
*   **78道MongoDB面试题**
*   **17道ansible面试题**
*   **60道dubbo面试题**
*   **53道kafka面试**
*   **18道mysql面试题**
*   **40道nginx面试题**
*   **77道redis面试题**
*   **28道zookeeper**

**总计 1000+ 道面试题， 内容 又全含金量又高**

*   **174道运维工程师面试题**

> 1、什么是运维?

> 2、在工作中，运维人员经常需要跟运营人员打交道，请问运营人员是做什么工作的?

> 3、现在给你三百台服务器，你怎么对他们进行管理?

> 4、简述raid0 raid1raid5二种工作模式的工作原理及特点

> 5、LVS、Nginx、HAproxy有什么区别?工作中你怎么选择?

> 6、Squid、Varinsh和Nginx有什么区别，工作中你怎么选择?

> 7、Tomcat和Resin有什么区别，工作中你怎么选择?

> 8、什么是中间件?什么是jdk?

> 9、讲述一下Tomcat8005、8009、8080三个端口的含义？

> 10、什么叫CDN?

> 11、什么叫网站灰度发布?

> 12、简述DNS进行域名解析的过程?

> 13、RabbitMQ是什么东西?

> 14、讲一下Keepalived的工作原理?

> 15、讲述一下LVS三种模式的工作过程?

> 16、mysql的innodb如何定位锁问题，mysql如何减少主从复制延迟?

> 17、如何重置mysql root密码?

**一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**
![img](https://img-blog.csdnimg.cn/img_convert/91bff40bf1bc2a7d439b4304ff7e000d.jpeg)

个端口的含义？

> 10、什么叫CDN?

> 11、什么叫网站灰度发布?

> 12、简述DNS进行域名解析的过程?

> 13、RabbitMQ是什么东西?

> 14、讲一下Keepalived的工作原理?

> 15、讲述一下LVS三种模式的工作过程?

> 16、mysql的innodb如何定位锁问题，mysql如何减少主从复制延迟?

> 17、如何重置mysql root密码?

**一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**
[外链图片转存中...(img-AHv1Tn0v-1712861892120)]