onemorepoint的博客

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...

测试数据 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...

solution 可以使用窗口函数进行筛选出按照日期每个日期出现的次数，然后筛选出来两个数值相等的记录来，当然可以通过join的方式进行查询 select workid,recdate, rectime, time4 from (select *, count(1) over(PARTITION by recdate ) num from T0916 ) a where a.num&...

测试数据 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...

测试数据 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...

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...

测试数据 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...

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,'发动机'...

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...

hope you can enjoy！ ???? mysql除法与取余数 解题思路 利用窗口函数，求出每行的排序，每个type下的行数，每个type下的汇总求和数 判断如果是type的最后一行，则将余数加到该行 SELECT a.id, a.type, CASE WHEN rk = num THEN mod_num + divide_num ELSE di...

????题目如下 测试数据 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 ...

???? ???? 测试数据 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...