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

本文链接：https://blog.csdn.net/weixin_45213266/article/details/121953991

根据查询结果删除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;