当前位置:网站首页>每日sql-员工奖金过滤和回答率排序第一
每日sql-员工奖金过滤和回答率排序第一
2022-08-11 05:36:00 【吃再多糖也不长胖】
每日sql 1-员工奖金
求出奖金少于1000的员工名字和奖金
DDL
Create table If Not Exists Employee (EmpId int, Name varchar(255), Supervisor int, Salary int);Create table If Not Exists Bonus (EmpId int, Bonus int);insert into Employee (EmpId, Name, Supervisor, Salary) values (3, 'Brad', null, 4000);insert into Employee (EmpId, Name, Supervisor, Salary) values (1, 'John', 3, 1000);insert into Employee (EmpId, Name, Supervisor, Salary) values (2, 'Dan', 3, 2000);insert into Employee (EmpId, Name, Supervisor, Salary) values (4, 'Thomas', 3, 4000);insert into Bonus (EmpId, Bonus) values (2, 500);insert into Bonus (EmpId, Bonus) values (4, 2000);
sql
select Employee.Name,Bonus.Bonus FROM Employee join Bonus on Employee.EmpId=Bonus.EmpId where Bonus.Bonus <1000 or Bonus.Bonus is null;
2-求出回答率最高的问题
一张表记录着每个问题的展示show和回答answer状态,如果没回答则用skip代替answer状态。求回答率最高的问题
DDL
Create table If Not Exists survey_log (uid int, action varchar(255), question_id int, answer_id int, q_num int, timestamp int);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 285, null, 1, 123);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'answer', 285, 124124, 1, '124');insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 369, null, 2, 125);insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'skip', 369, null, 2, 126);
sql
select question_id
from survey_log
group by question_id
order by sum(case when action ="answer" then 1 else 0 end )/sum(case when action = "show" then 1 else 0 end) desc limit 1;
边栏推荐
猜你喜欢
随机推荐
cloudreve使用体验
kill 命令
My meeting of the OA project (meeting seating & review)
姿态解算-陀螺仪+欧拉法
HCIP BGP neighbor building, federation, and aggregation experiments
HCIP WPN实验
torch.cat()用法
类的定义、类的继承以及super的使用
FusionCompute8.0.0实验(1)CNA及VRM安装
智能合约 ——— app评分合约
Concurrent programming in eight-part essay
华为防火墙-3-应用过滤
OA项目之我的审批(查询&会议签字)
HCIP实验(pap、chap、HDLC、MGRE、RIP)
arcmap下的多进程脚本
window7开启远程桌面功能
HCIP MPLS/BGP综合实验
HCIP MGRE\OSPF综合实验
八股文之mysql
概念名词解释