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