Mysql 练习
重点看:2、5、6、7、8、10、12、17、18、21、22、27、28、32、37、38、39、42、45、46、50、55、57、59、61、62、64、66、68、74、75
- 查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
select * from employees
order by hire_date desc
limit 1;
select * from employees
where hire_date = (
select max(hire_date) from employees
);
- 查找入职员工时间排名倒数第三的员工的所有信息**(可能存在多个)**
-- order by 排序
-- desc 从大到小
-- limit 2,1 从第二条开始读,读一条
-- limit 1 offset 2 偏移2读取1
select * from employees
where hire_date = (
select distinct hire_date
from employess
order by hire_date desc
limit 1 offset 2
);
select * from employees order by hire_date desc limit 2,1;
- 查找各个部门当前(dept_manager.to_date=‘9999-01-01’),领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from salaries inner join dept_manager
on salaries.emp_no = dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
order by salaries.emp_no;
- 查找所有已经分配部门的员工的last_name和first_name以及dept_no
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees inner join dept_emp
on dept_emp.emp_no = employees.emp_no;
- 查找所有员工的last_name和first_name以及对应部门编号dept_no,也包括暂时没有分配具体部门的员工
-- left join 左连接
select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left join dept_emp
on dept_emp.emp_no = employees.emp_no;
- 查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序(请注意,一个员工可能有多次涨薪的情况)
-- desc 逆序 从大到小
select employees.emp_no,salaries.salary
from employees left join salaries
where employees.emp_no = salaries.emp_no
and employees.hire_date = salaries.from_date
order by employees.emp_no desc;
- 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
-- 按照员工号分类
select emp_no,count(from_date) as t
from salaries
group by emp_no
having t > 15;
-- 下面代码正确 但是牛客网的环境不支持这样写
select a.emp_no, count(a.emp_no) t
from salaries a inner join salaries b
on a.emp_no=b.emp_no and a.to_date=b.from_date
where a.salary < b.salary
group by a.emp_no
having t>15
- 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
-- WHERE语句在GROUP BY语句之前,SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后,SQL会在分组之后计算HAVING语句。
-- Sql中过滤条件 on 和 where的区别 https://blog.csdn.net/u013468917/article/details/61933994
select salary from salaries
where to_date = '9999-01-01'
group by salary
order by salary desc;
- 获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
select dept_manager.dept_no,dept_manager.emp_no,salaries.salary
from dept_manager
inner join salaries on dept_manager.emp_no = salaries.emp_no
where salaries.to_date = '9999-01-01'
and dept_manager.to_date = '9999-01-01'
- 获取所有非manager的员工emp_no
select emp_no from employees
where emp_no not in
(select emp_no from dept_manager)
select employees.emp_no
from employees
left join dept_manager
on employees.emp_no = dept_manager.emp_no
where dept_manager.dept_no is null
- 获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
select dept_emp.emp_no,dept_manager.emp_no as manager_no
from dept_emp left join dept_manager
on dept_emp.dept_no = dept_manager.dept_no
where dept_manager.to_date='9999-01-01'
and dept_emp.emp_no <> manager_no
- 获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列。
select dept_emp.dept_no,dept_emp.emp_no, max(salaries.salary) as salary
from dept_emp inner join salaries
on dept_emp.emp_no = salaries.emp_no
where dept_emp.to_date = '9999-01-01'and salaries.to_date = '9999-01-01'
group by dept_emp.dept_no
order by dept_emp.dept_no
-- 上面代码由于用了group by,而d.emp_no是非聚合字段,所以不能出现在SELECT。
-- 一个聚合字段(dept_no)对应多个非聚合字段(emp_no),所以选择的时候,会随机选择非聚合字段中的任何一个,于是出错。
-- 如果强行写上述代码,当多人同时拥有最高薪水时也无法查出。
-- GROUP BY语句与HAVING语句的使用:参考资料:https://www.cnblogs.com/geogre123/p/11177204.html
-- 下面是规范代码
select d1.dept_no,d1.emp_no,s1.salary
from dept_emp d1 inner join salaries s1
on d1.emp_no = s1.emp_no
and d1.to_date = '9999-01-01'and s1.to_date = '9999-01-01'
where s1.salary in (
select max(s2.salary)
from dept_emp as d2 inner join salaries as s2
on d2.emp_no = s2.emp_no
and d2.to_date = '9999-01-01'and s2.to_date = '9999-01-01'
and d1.dept_no = d2.dept_no
)
order by d1.dept_no
select t.dept_no,t.emp_no,t.salary from
(
select de.dept_no,de.emp_no,s.salary,row_number()
over(partition by de.dept_no order by s.salary desc) as rk
from(select * from dept_emp where to_date='9999-01-01') de
inner join
(select * from salaries where to_date='9999-01-01') s
on de.emp_no=s.emp_no
)t
where t.rk=1;
- 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
select title,count(title) as t
from titles
group by title having t >= 2
- 从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算,title对应的数目t不增加)。
select title,count(distinct emp_no) as t
from titles
group by title
having t >= 2
- 查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列(题目不能使用mod函数)
select emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
where last_name <> 'Mary' and emp_no % 2 = 1
order by hire_date desc;
- 统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。
select titles.title,avg(salaries.salary)
from titles inner join salaries on titles.emp_no = salaries.emp_no
where titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by titles.title
- 获取当前(to_date=‘9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary**(可能有多个)**
select emp_no,salary
from salaries
where salary in (
select salary
from salaries where to_date = '9999-01-01'
group by salary
order by salary desc limit 1,1
)
-- 开窗函数
下面介绍三种用于进行排序的专用窗口函数:
1、RANK()
在计算排序时,若存在相同位次,会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,4······
2、DENSE_RANK()
在计算排序时,若存在相同位次,不会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如,有3条排在第1位时,排序为:1,2,3,4······
窗口函数用法:
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> )
select emp_no,salary
from(
-- 当薪水第二的人有多个时只会输出一个
-- select emp_no,salary,row_number() over(order by salary desc) t
-- 当薪水第二的人有多个时全部输出
select emp_no,salary,rank() over(order by salary desc) t
from salaries
where to_date='9999-01-01'
)
where t=2
- 查找当前薪水(to_date=‘9999-01-01’)排名第二多(可处理任意值)的员工编号emp_no、薪水salary、last_name以及first_name,你可以不使用order by完成吗
select salaries.emp_no,salaries.salary,employees.last_name,employees.first_name
from salaries left join employees
on salaries.emp_no = employees.emp_no
where to_date='9999-01-01'
and salaries.salary in (
select s1.salary
from salaries as s1inner join salaries s2
on s1.salary <= s2.salary
group by s1.salary
having count(distinct s2.salary) = 2
and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
)
- 查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工(三个表连接)
select employees.last_name,employees.first_name,tmp.dept_name
from employees left join (
select emp_no,dept_name
from dept_emp left join departments
on dept_emp.dept_no = departments.dept_no
)tmp
on employees.emp_no = tmp.emp_no
- 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
select max(salary) - min(salary)
from salaries
where emp_no = 10001
-- 按照时间排序 最后的-最前的
select(
select salary from salaries where emp_no = 10001
order by to_date desc limit 1
)-(
select salary from salaries where emp_no = 10001
order by to_date asc limit 1
)as growth
-
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!=‘9999-01-01’,这样的数据不显示在查找结果里面)
-- 思想与20一样 最后的 - 前面的
select salaries1.emp_no,(salaries1.salary - salaries2.salary) as growth
from(
select emp_no,salary from salaries where to_date = '9999-01-01'
) salaries1
inner join (
select salaries.emp_no,salaries.salary from salaries inner join employees
on salaries.emp_no = employees.emp_no
where employees.hire_date = salaries.from_date
) salaries2
on salaries1.emp_no = salaries2.emp_no
order by growth
- 统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum(三表联查)
select d.dept_no,d.dept_name,count(*) as sum
from salaries s inner join dept_emp de on s.emp_no=de.emp_no
inner join departments d on d.dept_no=de.dept_no
group by d.dept_no
-- 上面的有问题
select departments.dept_no,departments.dept_name,tmp.sum
from departments inner join
(
select dept_emp.dept_no,count(salaries.salary) as sum
from dept_emp inner join salaries
on dept_emp.emp_no = salaries.emp_no
group by dept_emp.dept_no
) tmp
on departments.dept_no = tmp.dept_no
- 对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
select emp_no,salary,dense_rank() over(order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc
- 获取所有非manager员工当前的薪水情况,给出dept_no、emp_no以及salary ,当前表示to_date=‘9999-01-01’
select dept_emp.dept_no,employees.emp_no,salaries.salary
from dept_emp
inner join employees on dept_emp.emp_no = employees.emp_no
inner join salaries on employees.emp_no = salaries.emp_no
where salaries.to_date='9999-01-01'
and employees.emp_no not in (
select emp_no from dept_manager
)
- 获取员工其当前的薪水比其manager当前薪水还高的相关信息,当前表示to_date=‘9999-01-01’,
结果第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
select emp.emp_no,manager.emp_no,emp.salary,manager.salary
from (
select dept_emp.emp_no,dept_emp.dept_no,salary
from salaries inner join dept_emp
on salaries.emp_no = dept_emp.emp_no
and salaries.to_date = '9999-01-01'
and dept_emp.to_date = '9999-01-01'
) emp
inner join
(
select dept_manager.emp_no,dept_manager.dept_no,salary
from salaries inner join dept_manager
on salaries.emp_no = dept_manager.emp_no
and salaries.to_date = '9999-01-01'
and dept_manager.to_date = '9999-01-01'
) manager
on emp.dept_no = manager.dept_no
where emp.salary > manager.salary
-
汇总各个部门当前员工的title类型的分配数目,即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count
(注:因为员工可能有离职,所有dept_emp里面to_date不为’9999-01-01’就已经离职了,不计入统计,而且员工可能有晋升,所以如果titles.to_date 不为 ‘9999-01-01’,那么这个可能是员工之前的职位信息,也不计入统计)
select departments.dept_no,departments.dept_name,titles.title,count(titles.title)
from departments,dept_emp,titles
where departments.dept_no = dept_emp.dept_no
and dept_emp.emp_no = titles.emp_no
and dept_emp.to_date = '9999-01-01'
and titles.to_date ='9999-01-01'
group by departments.dept_no,titles.title
-
给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth,并按照salary_growth逆序排列。
提示:在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年,而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)
select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1 inner join salaries s2
on s1.emp_no = s2.emp_no
and s1.to_date = s2.from_date
where s2.salary - s1.salary > 5000
order by salary_growth desc
select s1.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1
join salaries s2
on s1.emp_no=s2.emp_no
and (strftime('%Y', s2.to_date)-strftime('%Y', s1.to_date))=1
and (s2.salary-s1.salary)>5000
order by salary_growth desc;
- 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id)),而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
select c.name,count(f.film_id)
from film f,category c,film_category fc
where f.description like '%robot%'
and f.film_id = fc.film_id
and fc.category_id = c.category_id
and c.category_id in (
select category_id
from film_category
group by category_id
having count(film_id)>=5)
- 使用join查询方式找出没有分类的电影id以及名称
-- inner join 中where和on类似
-- 在left join 中on后面的条件只对右表有效 ,right join同理
-- where 对整个表过滤
select film.film_id,film.title
from film left join film_category
on film.film_id = film_category.film_id
where film_category.category_id is null
- 你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗
select title,description
from film
where film_id in(
select film_category.film_id
from film_category inner join category
on film_category.category_id = category.category_id
where category.name = 'Action'
)
- 将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
(注:sqllite,字符串拼接为 || 符号,不支持concat函数,mysql支持concat函数)
-- mysql
select concat(last_name," ",first_name) as Namefrom employees
-- sqlite
select (last_name||' '||first_name) as Name from employees
- 创建一个actor表,包含如下列信息
create table actor(
actor_id smallint not null primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null default(datetime('now','localtime'))
)
- 请你对于表actor批量插入如下数据(不能有2条insert语句哦!)
insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
- 对于表actor插入如下数据,如果数据已经存在,请忽略(不支持使用replace操作)
insert or ignore into actor
values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');
- 请你创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表.
create table if not exists actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null
);
insert into actor_name
select first_name,last_name from actor;
-
针对如下表actor结构创建索引:
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作,
mysql支持ALTER TABLE创建索引)
对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
create unique index uniq_idx_firstname on actor(first_name); create index idx_lastname on actor(last_name);
-
针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
create view actor_name_view as
select first_name as first_name_v,last_name as last_name_v
from actor;
- 针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
select * from salaries indexed by idx_emp_no where emp_no = 10005
- 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’2020-10-01 00:00:00’
alter table actor add column create_date datetime not null
default ('2020-10-01 00:00:00')
41.构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);
/*在MySQL中,创建触发器语法如下:
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中:
trigger_name:标识触发器名称,用户自行指定;
trigger_time:标识触发时机,取值为 BEFORE 或 AFTER;
trigger_event:标识触发事件,取值为 INSERT、UPDATE 或 DELETE;
tbl_name:标识建立触发器的表名,即在哪张表上建立触发器;
trigger_stmt:触发器程序体,可以是一句SQL语句,或者用 BEGIN 和 END 包含的多条语句,每条语句结束要分号结尾。
【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。
具体地:
在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;
在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;
*/
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end
- 删除emp_no重复的记录,只保留最小的id对应的记录。
delete from titles_test
where id not in (
select min(id) from titles_test
group by emp_no
)
- 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
-- update table set value where condition
update titles_test set to_date=NULL,from_date="2001-01-01"
where to_date = '9999-01-01'
- 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了。
-- reaplace函数 第一个参数为该字段的名称,第二参数为该字段的需要被修改值,第三个参数为该字段修改后的值
update titles_test set
emp_no = replace(emp_no,10001,10005)
where id = 5
- 将titles_test表名修改为titles_2017
/*
alter table 表名 add 列名/索引/主键/外键等;
alter table 表名 drop 列名/索引/主键/外键等;
alter table 表名 alter 仅用来改变某列的默认值;
alter table 表名 rename 列名/索引名 to 新的列名/新索引名;
alter table 表名 rename to/as 新表名;
alter table 表名 modify 列的定义但不改变列名;
alter table 表名 change 列名和定义都可以改变。
*/
alter table titles_test rename to titles_2017
- 在audit表上创建外键约束,其emp_no对应employees_test表的主键id。
-- alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
alter table audit
add constraint fk_no_id foreign key(emp_no)
references employees_test(id)
- 请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
update salaries set salary=salary*1.1
where to_date='9999-01-01'
and emp_no in (
select emp_no from emp_bonus
)
- 将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat,请用||实现,mysql支持concat)
select concat(last_name,"'",first_name) as name
from employees
- 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
select length("10,A,B") - length(replace("10,A,B",",",""))
- 获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
select first_name
from employees
order by substr(first_name,-2,2)
select first_name
from employees
order by right(first_name,2)
- 按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no,",") as employees
from dept_emp
group by dept_no
- 查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。
select avg(salary) from salaries
where to_date='9999-01-01'
and salary not in
(select max(salary) from salaries where to_date='9999-01-01')
and salary not in
(select min(salary) from salaries where to_date='9999-01-01')
- 分页查询employees表,每5行一页,返回第2页的数据
select * from employees limit 5,5
- 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received,没有分配奖金的员工不显示对应的bonus类型btype和received
select e.emp_no, de.dept_no, eb.btype, eb.received
from employees e
inner join dept_emp de on e.emp_no = de.emp_no
left join emp_bonus eb on e.emp_no = eb.emp_n
- 使用含有关键字exists查找未分配具体部门的员工的所有信息。
select * from employees
where not exists (select emp_no from dept_emp
where dept_emp.emp_no = employees.emp_no);
- 给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
-- case x when condition then else end
select employees.emp_no,employees.first_name,employees.last_name,
emp_bonus.btype ,salaries.salary,
(case emp_bonus.btype when 1 then 0.1*salaries.salary
when 2 then 0.2*salaries.salary
else 0.3*salaries.salary end)as bonus
from employees inner join emp_bonus on employees.emp_no=emp_bonus.emp_no
inner join salaries on employees.emp_no=salaries.emp_no
and salaries.to_date='9999-01-01'
select employees.emp_no,employees.first_name,employees.last_name,
emp_bonus.btype ,salaries.salary,
(case when emp_bonus.btype=1 then 0.1*salaries.salary
when emp_bonus.btype=2 then 0.2*salaries.salary
else 0.3*salaries.salary end)as bonus
from employees inner join emp_bonus on employees.emp_no=emp_bonus.emp_no
inner join salaries on employees.emp_no=salaries.emp_no
and salaries.to_date='9999-01-01'
- 按照salary的累计和running_total,其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和,其他以此类推。 具体结果如下Demo展示
select emp_no,salary,sum(salary) over(order by emp_no) as running_total
from salaries where to_date = '9999-01-01'
- 对于employees表中,输出first_name排名(按first_name升序排序)为奇数的first_name
select e1.first_name from employees e1
inner join
(select first_name,row_number() over(order by first_name) rk from employees) e2
on e1.first_name = e2.first_name
where e2.rk%2 == 1
select e1.first_name from employees e1
where(select count(*) from employees e2 where e1.first_name>=e2.first_name )%2 = 1
- d为用户主键id,number代表积分情况,让你写一个sql查询,积分表里面出现三次以及三次以上的积分
select number from grade
group by number
having count(number) >= 3
- 输出通过的题目的排名,通过题目个数相同的,排名相同,此时按照id升序排列,数据如下:
select id,number,dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank,id
select a.id,a.number,
(select count(distinct b.number) from passing_number b
where b.number>=a.number ) as t_rank
from passing_number a order by t_rank,a.id;
- 请你找到每个人的任务情况,并且输出出来,没有任务的也要输出,而且输出结果按照person的id升序排序
select person.id,person.name,task.content
from person left join task on person.id = task.person_id
order by person.id
- 每一个日期里面,正常用户发送给正常用户邮件失败的概率是多少,结果保留到小数点后面3位(3位之后的四舍五入),并且按照日期升序排序,上面例子查询结果如下:
select email.date,
round(sum (case email.type when 'completed' then 0 else 1 end)*1.0 / count(email.type),3)
from email
inner join user u1 on email.send_id = u1.id and u1.is_blacklist=0
inner join user u2 on email.receive_id = u2.id and u2.is_blacklist=0
group by email.date
- 请你写出一个sql语句查询每个用户最近一天登录的日子,并且按照user_id升序排序
select user_id,max(date) from login group by user_id
- 请你写出一个sql语句查询每个用户最近一天登录的日子,用户的名字,以及用户用的设备的名字,并且查询结果按照user的name升序排序
select user.name as u_n,client.name as c_n,login.date as d
from login
inner join user on user.id = login.user_id
inner join client on client.id = login.client_id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by user_id)
order by user.name
- 请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下
-- 注意distinst
select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);
- 请你写出一个sql语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序,
select l1.date,sum(case when(l1.user_id,l1.date) in
(select distinct l2.user_id,min(l2.date) from login l2 group by l2.user_id)
then 1 else 0 end)
from login l1
group by l1.date
select tmp.date,sum(case tmp.rk when 1 then 1 else 0 end) as new
from (select user_id,date,rank() over(partition by user_id order by date) as rk from login) tmp
group by tmp.date
- 请你写出一个sql语句查询每个日期新用户的次日留存率,结果保留小数点后面3位数(3位之后的四舍五入),并且查询结果按照日期升序排序
select t1.date,round(count(t2.user_id)/count(t1.user_id),3) p
from
(select user_id,min(date) date from login group by user_id) t1
left join (select user_id,date from login) t2
on t1.user_id = t2.user_id
and t2.date=date_add(t1.date,interval 1 day)
group by t1.date
union
select date,0.000 p from login
where date not in (select min(date) from login group by user_id)
order by date;
- 牛客每天有很多人登录,请你统计一下牛客每个用户查询刷题信息,包括: 用户的名字,以及截止到某天,累计总共通过了多少题。 不存在没有登录却刷题的情况,但是存在登录了没刷题的情况,不会存在刷题表里面,有提交代码没有通过的情况,但是会记录在刷题表里,只不过通过数目是0。请你写出一个sql语句查询刷题信息,包括: 用户的名字,以及截止到某天,累计总共通过了多少题,并且查询结果先按照日期升序排序,再按照姓名升序排序,有登录却没有刷题的哪一天的数据不需要输出,上面的例子查询结果如下:
select user.name as u_n, login.date, p1.ps_num
from login
inner join (select user_id,date,
sum(number) over (partition by user_id order by date) ps_num
from passing_number)p1
on login.user_id = p1.user_id and login.date=p1.date
inner join user on login.user_id = user.id
order by login.date,user.name
- 请你写一个sql语句查询各个岗位分数的平均数,并且按照分数降序排序,结果保留小数点后面3位(3位之后四舍五入):
select job,round(sum(score)*1.0/count(score),3) as avg
from grade group by job
order by avg desc
- 请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序,如下:
select g1.* from grade g1
inner join
(
select id,job,round(sum(score)*1.0/count(score),3) as avg
from grade group by job
order by avg desc
) g2
on g1.job = g2.job
where g1.score > g2.avg
order by g1.id
- 请你找出每个岗位分数排名前2的用户,得到的结果先按照language的name升序排序,再按照积分降序排序,最后按照grade的id升序排序,得到结果如下:
select g.id,l.name,g.score
from
(
select id, score,language_id, dense_rank() over(partition by language_id order by score desc) as rk
from grade
)g
inner join language l on g.language_id = l.id
where g.rk <=2
order by l.name,g.score desc,g.id
- 请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围,并且按job升序排序,结果如下:
select job,
case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2,0) end as 'start',
case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2+1,0) end as 'end'
from(
select job,count(*) as nums from grade group by job
)g
select job,
case when count(*)%2=0 then round(count(*)/2,0) else round((count(*)+1)/2,0) end as start,
case when count(*)%2=0 then round(count(*)/2+1,0) else round((count(*)+1)/2,0) end as end
from grade
group by job
- 请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息,并且按id升序排序,结果如下:
select g1.id,g1.job,g1.score,g1.ranking
from (
select id,job,score,row_number() over(partition by job order by score desc) as 'ranking'
from grade
)g1
inner join
(
select job,
case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2,0) end as 'start',
case when nums%2 = 1 then round((nums+1)/2,0) else round(nums/2+1,0) end as 'end'
from(
select job,count(*) as nums from grade group by job
)g2
)g3
on g1.job = g3.job
where g1.ranking between g3.start and g3.end
order by g1.id
末尾
- 现在数据库中有一张用户交易表order,其中有userid(用户ID)、orderid(订单ID)、amount(订单金额)、paytime(支付时间),请写出对应的SQL语句,查出每个月的新客数(新客指在严选首次支付的用户),当月有复购的新客数,新客当月复购率(公式=当月有复购的新客数/月总新客数)
create view t1 as
select userid,strftime('%Y-%m',min(paytime)) as year_month
from order group by userid
-- 每个月新客数
select t1.year_month,count(t1.userid) as '每月新客数'
from t1 group by t1.year_month
-- 当月有复购的新客数
select t2.year_month,count(t2.nums) as '复购的新客数'
from
(
select t1.year_month,count(o1.userid) as nums
from t1 inner join order o1
on o1.userid = t1.userid and strftime('%Y-%m',o1.paytime) = t1.year_month
group by o1.userid
having count(o1.userid) > 1
) as t2
group by t2.year_month
-- 新客当月复购率
select a.year_month, a.每月新客数, b.复购的新客数, 1.0*b.复购的新客数/a.每月新客数 as '当月复购率'
from
(
select t1.year_month,count(t1.userid) as '每月新客数'
from t1 group by t1.year_month
) as a
(
select t2.year_month,count(t2.nums) as '复购的新客数'
from
(
select t1.year_month,count(o1.userid) as nums
from t1,order o1
where o1.userid = t1.userid and strftime('%Y-%m',o1.paytime) = t1.year_month
group by o1.userid having count(o1.userid) > 1
)as t2
group by t2.year_month
) as b
where a.year_month=b.year_month
order by a.year_month