MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
  • 根据查询结果删除sql
  • 先查询要删除的数据 在套用一层select
 DELETE 
FROM
 bo_eu_sample
WHERE
 id  IN ( SELECT id  from  ( SELECT id  from bo_eu_sample WHERE CREATEDATE like '2021-12-15 10%' )aa );

去除重复id

DELETE 
FROM
 trees_current_device_info_sub9 
WHERE
 id NOT IN ( SELECT id FROM ( SELECT MAX( id ) AS id FROM trees_current_device_info_sub9 GROUP BY deviceId, createTime ) aa );
  • 新增对比前一条与后一条数据
-- 新增 trees_current_device_info_sub9 
INSERT INTO trees_statistics9(aid, bid, datea, dateb, pl, deviceId, astatus, bstatus)
  SELECT a.id aid, b.id bid, a.createTime datea, b.createTime  dateb, TIMESTAMPDIFF(SECOND, a.createTime, b.createTime) pl, a.deviceId, a.`status`, b.`status`
  FROM   (SELECT   @arownum := @arownum + 1 rownum, id, createTime, deviceId, `status`
          FROM     trees_current_device_info_sub9, (SELECT @arownum := 0) t
          
          ORDER BY deviceId, createTime DESC) a,
         (SELECT   @brownum := @brownum + 1 rownum, id, createTime, deviceId, `status`
          FROM     trees_current_device_info_sub9, (SELECT @brownum := 1) t
          ORDER BY deviceId, createTime DESC ) b
  WHERE  a.rownum = b.rownum AND a.deviceId = b.deviceId AND DATE_FORMAT(a.createTime, '%Y-%m-%d') = DATE_FORMAT(b.createTime, '%Y-%m-%d') 

修改sql

UPDATE student AS A
INNER JOIN (
SELECT
    MAX( sc.SCORE ) AS maxscore,
    sc.ID AS maxid,
    sc.`SUBJECT` maxsub 
FROM
    score sc
    LEFT JOIN student st ON sc.ID = st.ID 
GROUP BY
    sc.ID 
    ) AS B ON A.ID = B.maxid 
    SET A.ID = B.maxid,
    A.SUBJECT = B.maxsub,
    A.score = B.maxscore
  SELECT deviceId,pl,date_format(datea,'%Y年-%m月-%d日') monthNo,astatus, SUM(count) realTotalDur,(24*60*60*30 )/pl SUMCOUNT ,ROUND(SUM(count)/((24*60*60*30)/pl),3) dayaavailability
 FROM `trees_availability` tay
 LEFT JOIN trees_device td
 ON td.id = tay.deviceId
 LEFT JOIN trees_lab tl
 ON tl.id = td.labId
 LEFT JOIN trees_device_type tdt
 ON tdt.id = td.deviceTypeId
 LEFT JOIN trees_device_baisc_type tdbt
 ON tdbt.id = tdt.deviceBasicType
 WHERE (astatus=1 OR astatus=3 ) AND (pl=10 OR pl=30) 
AND deviceId =78 group by deviceId,pl ,astatus,date_format(datea,'%Y年-%m月-%d日') ORDER BY date_format(datea,'%Y年-%m月-%d日');

删除重复的电子邮箱


DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

查找空闲设备

SELECT
  deviceId 
FROM
  time 
WHERE
  ( DATE_FORMAT( startTime, '%Y-%m-%d' ) >= '2022-01-05' AND DATE_FORMAT( endTime, '%Y-%m-%d ' ) <= '2022-01-12' ) 
  OR ( DATE_FORMAT( startTime, '%Y-%m-%d ' ) <= '2022-01-05' AND DATE_FORMAT( endTime, '%Y-%m-%d ' ) >= '2022-01-12' ) 
  OR ( DATE_FORMAT( endTime, '%Y-%m-%d ' ) >= '2022-01-05' AND DATE_FORMAT( endTime, '%Y-%m-%d ' ) <= '2022-01-12' )
  

三种去重方法

-- distinct
--列出 task_id 的所有唯一值(去重后的记录)
select distinct task_id from task;
 -- 任务总数
 select count(distinct task_id) task_numfrom task;
 -- distinct 通常效率较低。它不适合用来展示去重后具体的值,一般与 count 配合用来计算条数。
 
 -- group by
 
 
 -- 列出 task_id 的所有唯一值(去重后的记录,null也是值)
select task_id
from task
group by task_id;

-- 任务总数
select count(task_id) task_num
from (select task_id
      from task
      group by task_id) tmp;
      
-- row_number 是窗口函数,语法如下:
-- row_number() over (partition by <用于分组的字段名> order by <用于组内排序的字段名>)

-- 在支持窗口函数的 sql 中使用
select count(case when rn=1 then task_id else null end) task_num
from (select task_id
       , row_number() over (partition by task_id order by start_time) rn
   from Task) tmp;

窗口函数

-- 查询表中第二高的薪水  limit N, M 从第 N 条记录开始, 返回 M 条记录
-- 返回 N 条记录 返回 N 条记录

SELECT (SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1) 
AS SecondHighestSalary


SELECT 
IFNULL((SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1), NULL)
AS SecondHighestSalary

-- 分数应按从高到低排列。
-- 如果两个分数相等,那么两个分数的排名应该相同。
-- 在排名相同的分数后,排名数应该是下一个连续的整数。


-- dense_rank() dense_rank()

select score as "Score",dense_rank() over (order by score desc) as "Rank" from scores;


-- rank()  函数会把要求排序的值相同的归为一组且每组序号一样,排序不会连续执行

select score as "Score",rank() over (order by score desc) as "Rank" from scores;


-- ROW_NUMBER()在排名是序号 连续 不重复,即使遇到表中的两个一样的数值亦是如此

select score as "Score",row_number() over (order by score desc) as "Rank" from scores;

-- Ntile(group_num) 将所有记录分成group_num个组,每组序号一样

select score as "Score",Ntile(3) over (order by score desc) as "Rank" from scores;


子查询修改语句

UPDATE aoyi_yqb_product_spus per,
( SELECT aoyi_spu_id FROM aoyi_yqb_product_spus WHERE source_platform_id = '1' AND product_status = '1' AND aoyi_spu_id IN ( SELECT aoyi_sku_id FROM aoyi_product_sn_stock_eight WHERE stock_count < 4 ) ) bb 
SET per.product_status = '0' 
WHERE
  per.aoyi_spu_id = bb.aoyi_spu_id;


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
SQL 关系型数据库 MySQL
MySQL8 窗口函数
MySQL 8 引入了窗口函数,这是一种强大的分析工具,可以在查询结果集中执行计算而无需将数据分组到多个输出行中。本文介绍了窗口函数的基本概念和使用方法,并通过几个实际案例展示了如何使用窗口函数进行成绩和排名统计、销售数据分析等操作。
MySQL8 窗口函数
|
6天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
20 3
|
6天前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
13 1
|
15天前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些?
在 MySQL 中,可以通过多种方法比较日期的大小,包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录,确保日期格式一致以避免错误。
|
29天前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
45 1
|
18天前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
32 0
|
18天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
30 0
|
1月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
45 0
|
1月前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法,包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用,以及数据库操作如创建、删除表,数据类型,插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容,读者将能更好地管理和操
13 0
|
8天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
22 4