每日一练
记录自己每天的练习题,以此来提升巩固技术
time丶sand
tzy1994jy
展开
-
请编写SQL查询每天早晨8:00到次日早晨8:00的数据量
【代码】请编写SQL查询每天早晨8:00到次日早晨8:00的数据量。原创 2023-08-10 17:06:30 · 830 阅读 · 0 评论 -
SQL每日一题(20220507)即将连续的日期列分成两列显示
原创 2022-05-21 18:19:00 · 248 阅读 · 0 评论 -
SQL每日一题(20220511)希望按照9999对POINTS的数据进行均等分,并将每次分配的余数记录到库
with a as (select *, floor(points / 9999) fl, 9999 x, mod(points, 9999) md from f0511)select id, points, x, (points - sum(y) over (partition by id order by rn)) thelastfrom (select *, row_number() over (partition by id) rn f...原创 2022-05-21 18:18:43 · 230 阅读 · 0 评论 -
SQL每日一题(20211125)将每个月的数据汇总,并动态的横向显示
SQL每日一题(20220429) SELECT( SELECT GROUP_CONCAT(CONCAT('SUM(IF(d =', d, ',wt,0)) as ', concat(d, '日'))) from (select distinct day(dt) as d from t1125) t)原创 2022-05-20 08:37:32 · 873 阅读 · 0 评论 -
SQL每日一题(20211124)每个用户从第一次购买,计算其7天内的购买数量,然后再以此类推计算后面每隔7天的购买数量
SELECT d.id, d.date, d.numFROM (SELECT c.dt, c.id, sum(c.num) num, min(c.date) date FROM (SELECT a.id, a.da, b.date, FLOOR((b.date - a.da) / 7) dt, b.num FROM (SELECT id, min(date) da, sum(num) num FROM t1124 GROUP BY id) a ...原创 2022-05-20 08:36:50 · 566 阅读 · 0 评论 -
SQL每日一题(20220331)希望求出每个回环的开始和结尾的记录
with recursive t1 as(select c.*,c.old oldestfrom f0331 c where sn=1union allselect b.*,case a.new when b.old then a.oldest else b.old endfrom t1 a,f0331 b where b.sn=a.sn+1)select (select d.OLD from F0331 d where d.SN=min(c.sn)) as kt, (se..原创 2022-05-20 08:36:34 · 87 阅读 · 0 评论 -
SQL每日一题(20220323)希望得到0或1连续的区间的第一条记录
select b.type, min(b.ltime) ltimefrom (select a.*, cast(row_number() over (partition by a.type order by a.ltime) as signed) a, cast(row_number() over (order by a.ltime) as signed) b, cast(row_n...原创 2022-05-20 08:36:11 · 72 阅读 · 0 评论 -
SQL每日一题(20210716)查询中位数
select x.id,x.company,x.salary from(select t.*,count(1) over(partition by t.company) cnt,row_number() over(partition by t.company order by t.salary) rn from t0716 t) x where abs(x.rn - (cnt+1)/2)<1;原创 2022-05-20 08:35:53 · 61 阅读 · 0 评论 -
SQL每日一题(20220217)要求得到结果为 (取所有记录中Uname的Price的最大值,唯一一条数据列出)
select * from F0217 a where price>=all(select price from F0217 b where uname=a.uname)SELECT *FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY uname ORDER BY price DESC) xhh FROM F0217) tempWHERE temp.xhh = 1select * from f0217 where (uname,p..原创 2022-05-20 08:35:26 · 70 阅读 · 0 评论 -
SQL每日一题(20220211)查询F0211(ID,Name)表中第6至10条记录,ID作为主键可能不是连续增长的列
select ID,name from(select *,ROW_NUMBER()over(order by id) r from F0211)t where r between 6 and 10原创 2022-05-20 08:34:55 · 228 阅读 · 0 评论 -
SQL每日一题(20211220)希望得到如下结果
SELECT a.name one, b.name two, c.name threeFROM t1220 a, t1220 b, t1220 cWHERE a.id = b.parentid AND b.id = c.parentid原创 2022-05-20 08:34:27 · 70 阅读 · 0 评论 -
SQL每日一题(20211214)希望查询出至少连续出现4次的数字
SELECT NUMFROM (SELECT *,ROW_NUMBER() over (order by ID) a , ROW_NUMBER() over (PARTITION BY NUM ORDER BY ID) b , ROW_NUMBER() over (order by ID) - ROW_NUMBER() over (PARTITION BY NUM ORDER BY ID) FZ FROM T1214) T1GROUP BY FZ,..原创 2022-05-20 08:34:01 · 194 阅读 · 0 评论 -
SQL每日一题(20211129)要求出每个产品的具体成本是多少
WITH recursive cte AS (SELECT a.*, replace(level, '.', '') leve, ROW_NUMBER() over () - replace(level, '.', '') num, cost FROM t1129a a LEFT JOIN t1129b b ON a.ingre = b.ingre), tmp AS (SELECT item, .原创 2022-05-20 08:33:32 · 276 阅读 · 0 评论 -
SQL每日一题(20210809)如何查询出Data列含有数字8的数据
SELECT id, data, FIND_IN_SET('8', data) a FROM t0809 WHERE FIND_IN_SET('8', data)>0原创 2022-05-19 08:49:02 · 407 阅读 · 0 评论 -
SQL每日一题(20210929)依次得出最新单价,上一次单价,上上次单价,如果不存在则用0替代
WITH t1 AS(SELECT *, row_number() over (PARTITION by 物品 ORDER BY 购买日期 DESC) rnkFROM t0929)SELECT 物品,IFNULL(sum(IF(rnk=1,单价,0)),0) 最新,IFNULL(sum(IF(rnk=2,单价,0)),0) 上次,IFNULL(sum(IF(rnk=3,单价,0)),0) 上上次FROM t1GROUP BY 物品...原创 2022-05-19 08:48:46 · 590 阅读 · 0 评论 -
SQL每日一题(20211112)希望将T1112A和T1112B中的数据进行关联后对发票进行拆分
with un as (SELECT *, SUM(val) over (partition by name order by ttype) vsum FROM t1112a UNION ALL SELECT *, SUM(val) over (partition by name order by ttype) vsum FROM t1112b), rn...原创 2022-05-19 08:48:33 · 93 阅读 · 0 评论 -
SQL每日一题(20211029)如果总数量超过A的总数量,则多余的数量,叠加到最后一笔,不足则按剩余数显示。
Select *,Sum(QTY) Over (Partition By PCode Order By Itemno) a , If(500 > Sum(QTY) Over (Partition By PCode Order By Itemno), Qty, IF(Sum(QTY) Over (Partition By PCode Order By Itemno) - QTY < 500, 500 - Sum(Q..原创 2022-05-19 08:48:21 · 79 阅读 · 0 评论 -
SQL每日一题(20211028)即将每组CODE中缺少的日期,用上一个日期(RQ)所对应的数量(CNT)补齐
select t3.rq1 as rq, t3.code, t3.cntfrom ( select t2.*, date_add(t2.rq, interval rn - 1 day) as rq1 from ( select t1.*, row_number() over (partition by rq,cod...原创 2022-05-19 08:48:02 · 150 阅读 · 0 评论 -
SQL每日一题(20211022)1、对每行SNO出现的N1~N5的数出现的次数进行排序,出现的越多排在最前面 2、如果次数相同,则较大的排在前面
SQL每日一题(20220413)原创 2022-05-19 08:47:20 · 100 阅读 · 0 评论 -
SQL每日一题(20210726)要求去年销售少于10本的书籍,不包括从今天起1个月内可供使用的书籍。假设今天是2019-06-23。
SELECT T.BOOK_ID,T.NAMEFROM ( SELECT B.BOOK_ID, B.NAME, SUM(ifnull(O.QUANTITY,0)) AS SQ FROM TBOOKS B LEFT JOIN TORDERS O ON B.BOOK_ID = O.BOOK_ID AND YEAR(O.DISPATCH_DATE) = YEAR(timestampadd(YEAR,-1,'2019-06-23')) WHERE timestampdiff(da...原创 2022-05-19 08:47:00 · 102 阅读 · 0 评论 -
SQL每日一题(20210715)当001值是‘否’且002子项为‘空’时均不显示记录
select * from t0715 where answer is null and id not in (select id from t0715 where answer = '否')SELECT a.*,b.ANSWERFROM T0715 a JOIN T0715 b ON a.ID = b.ID AND a.`CODE` != b.`CODE`WHERE (a.`ANSWER` IS NULL AND b.`ANSWER` = '是') OR (a.`A..原创 2022-05-19 08:46:41 · 83 阅读 · 0 评论 -
SQL每日一题(20210713)要求出同一台机器的状态转换次数,例如1001机器从,开机到关机算一次,从关机再到待机算一次,合计2次
select MechineID, count(status) - 1 as cnt from T0713 as Twhere not exists(select null from T0713 as S where S.ID = T.ID + 1 and S.MechineID = T.MechineID and S.status = T.status)group by MechineID;with t as(selec..原创 2022-05-19 08:46:24 · 95 阅读 · 0 评论 -
SQL每日一题(20211008)即求出每个年级各学生的排名,以及每个年级不同班级的排名。
with cte as (select a.*, avg(分数) over (partition by 年级, 班级) avg_分数 from T1008 a)select a.*, dense_rank() over (partition by 年级 order by 分数 desc) 年级内排名, dense_rank() over (partition by 年级 order by avg_分数 desc) 年级内班级排名from cte aorder...原创 2022-05-19 08:46:08 · 687 阅读 · 0 评论 -
SQL每日一题(20210706)即将每天次数大于1的药品,按数量进行均等分
SELECT T1.组号,T1.药品名,T1.数量/T1.每天次数 AS 数,T1.每次更换FROM T0706 T1 LEFT JOIN mysql.help_topic T2 ON T1.每天次数 > T2.help_topic_id原创 2022-05-18 17:02:28 · 123 阅读 · 0 评论 -
SQL每日一题(20210924)找到只出现过一次的数字中,最大的那个数字
select num,count(1) c from T0924 GROUP BY num HAVING c =1 ORDER BY num desc limit 0,1select max(num)from (select num, count(rn) count_rn from (select num, row_number() over (partition by num order by num) rn from T0924) a group by nu..原创 2022-05-18 17:01:50 · 266 阅读 · 0 评论 -
SQL每日一题(20210917)找出每个部门获得前三高工资的所有员工
with tt2 as (select a.*,b.Name ,dense_rank() over(partition by b.name order by a.Salary desc) rk from t0917 a left join t0917b b on a.DepartmentID=b.id)select * from tt2 where rk<=3;原创 2022-05-18 17:01:05 · 144 阅读 · 0 评论 -
SQL每日一题(20210914)同一个CODE组,DATE从小到大排序,需要实现每个日期到下一个日期的前一天作为结束日期
SELECT code, invtp, date start_date, LEAD( date, 1, '9999-12-31' ) over ( PARTITION BY code ORDER BY invtp ) end_dateFROM t0914;原创 2022-05-18 17:00:30 · 128 阅读 · 0 评论 -
SQL每日一题(20210901)如果员工的【入职日期】是当月15号后,则该员工的【社保缴纳月份】为【入职日期】的次月
select *, case when t0901.类别 = '普通' then date_add(入职日期, interval 3 month) else date_add(入职日期, interval 6 month) end as 转正日期, case when day(date(入职日期)) <= 15 then date_format(入职日期, '%..原创 2022-05-18 16:59:00 · 388 阅读 · 0 评论 -
SQL每日一题(20210824)假设计算日期是从2021-7-13开始计算,PENDING_DAYS表示到货还需要的天数
select a.item,a.qty,case when a.qty<=b.qty then '在途' else '缺货' end status,case when a.qty<=b.qty then riqi - cast('2021-7-13' as date) else 0 end pending_daysfrom t0824a a ,(select item ,sum(qty) as qty,max(arrival_dt) as riqi from t0824b gr...原创 2022-05-18 16:57:53 · 160 阅读 · 0 评论 -
SQL每日一题(20210820)任意几个数的和大于95并且小于100
SQL每日一题(20220506)原创 2022-05-18 16:57:18 · 188 阅读 · 0 评论 -
SQL每日一题(20210818)取到每段连续日期的起始日期,终止日期,持续天数以及起始日期距上一期终止日期间隔的天数
WITH cte as (SELECT *, last_value(rq) over (partition by (day(rq) - id)) nq FROM t0818), temp as (SELECT min(rq) rq, nq FROM cte GROUP BY nq)SELECT rq, nq,lag (nq, 1, rq) over () a, datediff(nq, rq)+1 a, datediff(rq, lag (nq, 1, rq) over ()) b FR...原创 2022-05-18 16:56:27 · 269 阅读 · 0 评论 -
SQL每日一题(20210707)现在需要查询TripsID相同情况下,SetTime获取到一个月以后的最小最大数据
select tripsid, adddate(min(settime),1)settime1, adddate(max(settime),1)settime2 from t0707group by tripsid;原创 2022-05-18 16:55:30 · 70 阅读 · 0 评论 -
SQL每日一题(20210623)同一个CODE组,DATE从小到大排序,需要实现每个日期到下一个日期的前一天作为结束日期
SELECT CODE,INVTP,DATE AS START_DATE,LEAD(DATE,1,'9999-12-31')OVER(PARTITION BY CODE ORDER BY DATE) AS END_DATEFROM T0623原创 2022-05-18 16:54:53 · 107 阅读 · 0 评论 -
SQL每日一题(20210624)希望得到如下结果
SELECT T.*, (SELECT S.微信用户订单号 FROM T0624B S WHERE S.用户编号 LIKE concat('%', T.用户编号, '%')) AS 微信用户订单号FROM T0624A Tselect a.*,b.微信用户订单号 from t0624a a left join (select aa.用户编号, aa.用户地址, aa.金额, bb.微信用户订单号,find_in_set(aa.用户编号, bb.用户编号) afrom T0624a aa, ..原创 2022-05-17 19:50:46 · 91 阅读 · 0 评论 -
SQL每日一题(20210609)查询出既学过‘001‘课程,也学过‘003‘号课程的学生ID
select stuidfrom (select *, if(cid in ('001', '003'), 1, 0) s from T0609) sgroup by stuidhaving sum(s) = 2select stuid from T0609 where cid in('001','003') group by stuid having count(stuid) =2原创 2022-05-17 19:50:34 · 377 阅读 · 0 评论 -
SQL每日一题(20210607)求出在每一个工资发放日,每个部门的平均工资与公司的平均工资的比较结果 (高 / 低 / 相同)
SELECT b.pay_date, b.department_id, CASE WHEN b.avgb > a.avga THEN 'HIGHER' WHEN b.avgb = a.avga THEN 'SAME' ELSE 'LOWER' END comparisonFROM (SELECT date_format(ta.pay_date, '%Y-%m') pa...原创 2022-05-17 19:50:19 · 483 阅读 · 0 评论 -
SQL每日一题(20210608)查询出B列中完全存在于A列的记录
SELECT *,LOCATE(B, A) FROM T0608WHERE LOCATE(B, A);SELECT * FROM T0608 WHERE A LIKE CONCAT('%',B,'%')select *,instr(t.a,t.b) from T0608 t where instr(t.a,t.b)>0原创 2022-05-17 19:50:02 · 317 阅读 · 0 评论 -
SQL每日一题(20210604)找到当选者的名字,即选票最多的候选者
SELECT NAME FROM ( SELECT NAME,RANK()OVER(ORDER BY NUM DESC) AS RK FROM ( SELECT A.ID, A.Name, COUNT(*) AS NUM FROM T0604B B LEFT JOIN T0604A A ON B.CandidateID = A.ID GROUP BY A.ID, A.Name )T1 )T2WHERE RK = 1WITH c AS (...原创 2022-05-17 19:49:39 · 165 阅读 · 0 评论 -
SQL每日一题(20210601)如何将相邻两条记录的Name进行位置交换
select row_number() over(order by id),namesfrom(select if(id%2=0,id-1,id+1) id,namesfrom t0601 order by id)cselect case when (ID % 2 != 0 and ID != (select count(*) from T0601)) then ID + 1 when ID % 2 = 0 then ID - 1 ..原创 2022-05-17 19:49:27 · 275 阅读 · 0 评论 -
SQL每日一题(20210528)写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
select actor_id, director_idfrom T0528group by actor_id, director_idhaving count(1) >= 3原创 2022-05-17 19:49:14 · 192 阅读 · 0 评论