![](https://img-blog.csdnimg.cn/20190918140158853.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
mysql每日一题
mysql每日一题与、Leetcode刷题、Newcode刷题
onemorepoint
mysql、python、JavaScript、vue 学习中
展开
-
mysql每日一题- 部分top3工资查询
solution SELECT a.employee, a.department, a.Salary FROM ( SELECT *, DENSE_RANK() over ( PARTITION BY department ORDER BY Salary DESC ) rk FROM ( SELECT a.NAMES employee, b.NAME departm...原创 2021-09-17 14:40:00 · 137 阅读 · 0 评论 -
mysql每日一题0809--每行包含某个特定数据或者字符 like regex like length
测试数据 CREATE TABLE T0809 ( ID INT, DATA VARCHAR(10) ); INSERT INTO T0809 VALUES(1,'8'), (2,'88'), (3,'7,8'), (4,'6,7,8'), (5,'8,9'), (6,'7,88') solution select * from T0809 where LENGTH(data)-LE...原创 2021-08-10 18:35:00 · 70 阅读 · 0 评论 -
mysql每日一题-窗口函数练习0916
solution 可以使用窗口函数进行筛选出按照日期每个日期出现的次数,然后筛选出来两个数值相等的记录来,当然可以通过join的方式进行查询 select workid,recdate, rectime, time4 from (select *, count(1) over(PARTITION by recdate ) num from T0916 ) a where a.num&...原创 2021-09-16 18:15:00 · 194 阅读 · 0 评论 -
mysql每日一题0728---连续多天数据汇总统计
测试数据 CREATE TABLE T0728 ( val VARCHAR(50) ); insert into T0728 (val) values('A10000003'); insert into T0728 (val) values('A10000001'); insert into T0728 (val) values('A10000002'); insert int...原创 2021-07-28 15:24:00 · 112 阅读 · 0 评论 -
mysql每日一题- 动态行转列
测试数据 CREATE TABLE T0918 ( No INT, NAME NVARCHAR(20), age INT); INSERT INTO T0918 VALUES (1,'张三','18'); INSERT INTO T0918 VALUES (1,'李四','17'); INSERT INTO T0918 VALUES (1,'王五','23'); INSERT INTO...原创 2021-09-24 12:07:00 · 90 阅读 · 0 评论 -
mysql每日一题0723----- 关联查询然后行转列统计
hope you can enjoy ???? ! CREATE TABLE T0723A ( ID INT, 机房 VARCHAR(20) ); CREATE TABLE T0723B ( ID INT, 机房ID INT, 主机名称 VARCHAR(20), 主机状态 INT ); INSERT INTO T0723A VALUES (1,'机房A'); INSERT INTO T072...原创 2021-07-23 11:17:00 · 110 阅读 · 0 评论 -
mysql每日一题0708--- 临近值补全数据
测试数据 CREATE TABLE T0708 (LDate DATE NOT NULL, Value1 INT NULL, Value2 INT NULL ) INSERT INTO T0708 VALUES('2020-11-25', 500 ,200); INSERT INTO T0708 VALUES('2020-11-24', Null, 200); INSERT INTO T0...原创 2021-07-27 19:08:00 · 111 阅读 · 0 评论 -
mysql每日一题0701----- 层级显示--递归的方法
CREATE TABLE T0701( ID INT, PRODUCTNAME VARCHAR(64), PARENTID INT, ); INSERT INTO T0701 VALUES ( 1,'汽车',NULL); INSERT INTO T0701 VALUES ( 2,'车身',1); INSERT INTO T0701 VALUES ( 3,'发动机'...原创 2021-07-27 19:42:00 · 184 阅读 · 0 评论 -
mysql每日一题0722--各部门人数
hope you can enjoy ???? ! ???? 测试数据 CREATE TABLE 部门表(ID int,部门 VARCHAR(10),父ID int); CREATE TABLE 职工表(部门 VARCHAR(10),姓名 VARCHAR(10)); INSERT INTO 部门表 VALUES(1,'A公司',null); INSERT INTO 部门表 VALUES(2,'A...原创 2021-07-22 15:15:00 · 639 阅读 · 0 评论 -
mysql每日一题0727--窗口函数-mysql除法,取余数
hope you can enjoy! ???? mysql除法与取余数 解题思路 利用窗口函数,求出每行的排序,每个type下的行数,每个type下的汇总求和数 判断如果是type的最后一行,则将余数加到该行 SELECT a.id, a.type, CASE WHEN rk = num THEN mod_num + divide_num ELSE di...原创 2021-07-27 12:36:00 · 628 阅读 · 0 评论 -
mysql每日一题0721 关联查询
????题目如下 测试数据 CREATE TABLE T0721 ( DOCNUM INT, STATUS VARCHAR(26) ); INSERT INTO T0721 VALUES (33,'FULL'); INSERT INTO T0721 VALUES (33,'NOFULL'); INSERT INTO T0721 VALUES (34,'FULL'); INSERT INTO ...原创 2021-07-21 10:21:00 · 143 阅读 · 0 评论 -
mysql每日一题0720--窗口函数-连续多天统计
???? ???? 测试数据 CREATE TABLE T0720 ( UID INT NOT NULL , CALLBACK_DATE DATE NOT NULL ); INSERT INTO T0720 VALUES (1,'2020-4-1'); INSERT INTO T0720 VALUES (1,'2020-4-5'); INSERT INTO T0720 VALUES (1,'20...原创 2021-07-20 10:29:00 · 218 阅读 · 0 评论