哆啦kkaai

题解

/ 注册

全部文章 / 题解（共16篇）

题解 | #牛客每个人最近的登录日期(四)#

select date ,sum(if(jici=0,1,0)) as new from (select *,min(date) over(partition by user_id) as log, timestampdiff(day,min(date) over(partition by user...

Mysql

2021-10-13

0 361

题解 | #牛客每个人最近的登录日期(三)#

select round((count(distinct user_id)/(select count(distinct user_id) from login)),3) as p from (select * ,min(date) over (partition by user_id) as ...

Mysql

2021-10-13

0 333

题解 | #邮件异常概率#

select date, round(count(if(type='no_completed',id,null))/count(email.id),3) as p from email where send_id not in (select id from user where is_black...

Mysql

2021-10-08

0 350

题解 | #获取有奖金的员工相关信息。#

select e.emp_no,first_name,last_name,btype,salary, case when btype=1 then salary*0.1 when btype=2 then salary*0.2 else salary*0.3 end as ...

Mysql

2021-10-05

0 278

题解 | #平均工资#

select avg(salary) as avg_salary from salaries where salary not in (select max(salary) as sal from salaries where to_date='9999-01-01' union select ...

Mysql

2021-10-04

0 316

题解 | #按照dept_no进行汇总#

select dept_no,GROUP_CONCAT(emp_no order by emp_no separator ',') as employees from dept_emp group by dept_no order by dept_no; 运用的是分组合并函数：group_con...

Mysql

2021-10-04

0 407

题解 | #查找字符串 10,A,B 中逗号,出现的次数cnt#

select length('10,A,B')-length(replace('10,A,B',',','')) as cnt; select length('10,A,B'); -- 得出整体字符串长度 select replace('10,A,B',',',''); -- 以‘’来替换逗...

Mysql

2021-10-04

0 334

题解 | #将employees表中的所有员工的last_name和first_name通过引号连接起来。#

select concat_ws('\'',last_name,first_name) from employees; 比使用concat运行要快。 select concat(last_name,''',first_name) from employees;

Mysql

2021-10-04

0 302

题解 | #将所有获取奖金的员工当前的薪水增加10%#

UPDATE salaries set salary=salary*1.1 where to_date='9999-01-01'and emp_no in (select emp_no from emp_bonus); 刚开始用的是另一种代码： select sal.emp_no from sal...

Mysql

2021-10-04

0 366

题解 | #批量插入数据，不使用replace操作#

UPDATE actor set first_name='WD' where first_name='ED'; UPDATE actor set last_name='CHASE' where first_name='GUINESS'; 修改数据update（属于DML数据操作语言）语法格式：...

Mysql

2021-09-26

0 285