MYSQL
查找目前薪水排名第二多的員工
查找目前薪水(to_date=‘9999-01-01’)排名第二多的員工編号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成嗎
CREATE TABLE
employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (
emp_no
));
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
from_date
));
/*
嵌套子查詢+聚合函數
*/
select s.emp_no, s.salary, e.last_name, e.first_name
from employees e join salaries s
on e.emp_no = s.emp_no
where s.salary =
(
select max(salary)
from salaries
where salary <
(
select max(salary)
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and to_date = '9999-01-01'
select s.emp_no, s.salary, e.last_name, e.first_name
from employees e join salaries s
on e.emp_no = s.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2
on s1.salary <= s2.salary
and s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.salary
having count(distinct s2.salary) = 2
)
and s.to_date = '9999-01-01'
/*
以s1進行分組，每個組中數字的個數，就是s1排的名次
*/
對所有員工的薪水按照salary進行按照1-N的排名
有一個薪水表salaries簡況如下:
對所有員工的薪水按照salary進行按照1-N的排名，相同salary并列且按照emp_no升序排列：
select emp_no, salary, dense_rank() over (order by salary desc) as t_trank
from salaries
order by t_trank, emp_no
/*
視窗函數
*/
select s1.emp_no, s1.salary, count(distinct s2.salary) as t_trank
from salaries s1 join salaries s2
on s1.salary <= s2.salary
group by s1.emp_no
order by s1.salary desc, s1.emp_no
擷取所有非manager員工目前的薪水情況
擷取所有非manager員工目前的薪水情況，給出dept_no、emp_no以及salary ，目前表示to_date=‘9999-01-01’
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (
emp_no
));
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
from_date
));
select de.dept_no, de.emp_no, s.salary
from salaries s, dept_emp de
where s.emp_no = de.emp_no and s.emp_no not in (
select emp_no
from dept_manager
where to_date = '9999-01-01'
) and s.to_date = '9999-01-01'
擷取員工其目前的薪水比其manager目前薪水還高的相關資訊
擷取員工其目前的薪水比其manager目前薪水還高的相關資訊，目前表示to_date=‘9999-01-01’,
結果第一列給出員工的emp_no，
第二列給出其manager的manager_no，
第三列給出該員工目前的薪水emp_salary,
第四列給該員工對應的manager目前的薪水manager_salary
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
from_date
));
select de.emp_no, dm.emp_no manager_no, s1.salary emp_salary, s2.salary manager_salary
from dept_emp de, dept_manager dm, salaries s1, salaries s2
where de.dept_no = dm.dept_no
and de.emp_no = s1.emp_no
and dm.emp_no = s2.emp_no
and s1.salary > s2.salary
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
查找描述資訊中包含robot的電影對應的分類名稱以及電影數目
題目描述
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT ‘0’,
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL,
last_update
timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL,
last_update
timestamp);
查找描述資訊(film.description)中包含robot的電影對應的分類名稱(category.name)以及電影數目(count(film.film_id))，而且還需要該分類包含電影總數量(count(film_category.category_id))>=5部.
注：電影數目>=5 是這類電影的所有數目，并不是包含了robot的這類電影的數目
select c.name `分類名稱category.name`, count(f.film_id) as `電影數目count(film.film_id)`
from film f, category c, film_category fc
where f.description like '%robot%'
and f.film_id = fc.film_id
and c.category_id = fc.category_id
and c.category_id in
(select category_id
from film_category
group by category_id
having count(film_id) >= 5
)
擷取所有員工的emp_no、部門編号dept_no以及對應的bonus類型btype和received
擷取所有員工的emp_no、部門編号dept_no以及對應的bonus類型btype和received，沒有配置設定獎金的員工不顯示對應的bonus類型btype和received
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
CREATE TABLE
emp_bonus
(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);
CREATE TABLE
employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (
emp_no
));
select e.emp_no `e.emp_no`, de.dept_no, eb.btype, eb.received
from emp_bonus eb right join employees e
on e.emp_no = eb.emp_no, dept_emp de
where e.emp_no = de.emp_no
擷取有獎金的員工相關資訊
擷取有獎金的員工相關資訊。
CREATE TABLE
employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (
emp_no
));
CREATE TABLE
dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
dept_no
));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL, PRIMARY KEY (
emp_no
,
from_date
));
給出emp_no、first_name、last_name、獎金類型btype、對應的目前薪水情況salary以及獎金金額bonus。 bonus類型btype為1其獎金為薪水salary的10%，btype為2其獎金為薪水的20%，其他類型均為薪水的30%。 目前薪水表示to_date=‘9999-01-01’
select eb.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (s.salary * eb.btype * 0.1) bonus
from emp_bonus eb left join employees e on eb.emp_no = e.emp_no, salaries s
where eb.emp_no = s.emp_no and s.to_date = '9999-01-01'
統計salary的累積和
按照salary的累計和running_total，其中running_total為前N個目前( to_date = ‘9999-01-01’)員工的salary累計和，其他以此類推。 具體結果如下Demo展示。。
CREATE TABLE
salaries
(
emp_no
int(11) NOT NULL,
salary
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (
emp_no
,
from_date
));
視窗函數
select emp_no, salary, sum(salary) over (order by emp_no) running_total
from salaries
where to_date = '9999-01-01'
select s1.emp_no, s1.salary, sum(s2.salary) running_total
from salaries s1 inner join salaries s2
on s2.emp_no <= s1.emp_no
where s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
group by s1.emp_no
對于employees表中，輸出first_name排名為奇數的first_name
對于employees表中，輸出first_name排名(按first_name升序排序)為奇數的first_name
CREATE TABLE
employees
(
emp_no
int(11) NOT NULL,
birth_date
date NOT NULL,
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL,
gender
char(1) NOT NULL,
hire_date
date NOT NULL,
PRIMARY KEY (
emp_no
));
如，輸入為：
INSERT INTO employees VALUES(10001,‘1953-09-02’,‘Georgi’,‘Facello’,‘M’,‘1986-06-26’);
INSERT INTO employees VALUES(10002,‘1964-06-02’,‘Bezalel’,‘Simmel’,‘F’,‘1985-11-21’);
INSERT INTO employees VALUES(10005,‘1955-01-21’,‘Kyoichi’,‘Maliniak’,‘M’,‘1989-09-12’);
INSERT INTO employees VALUES(10006,‘1953-04-20’,‘Anneke’,‘Preusig’,‘F’,‘1989-06-02’);
select e1.first_name
from employees e1
where
(
select count(*)
from employees e2
where e1.first_name >= e2.first_name
) % 2 = 1
異常郵件的機率
現在有一個需求，讓你統計正常使用者發送給正常使用者郵件失敗的機率:
有一個郵件(email)表，id為主鍵， type是枚舉類型，枚舉成員為(completed，no_completed)，completed代表郵件發送是成功的，no_completed代表郵件是發送失敗的。簡況如下:
第1行表示為id為2的使用者在2020-01-11成功發送了一封郵件給了id為3的使用者;
…
第3行表示為id為1的使用者在2020-01-11沒有成功發送一封郵件給了id為4的使用者;
…
第6行表示為id為4的使用者在2020-01-12成功發送了一封郵件給了id為1的使用者;
下面是一個使用者(user)表，id為主鍵，is_blacklist為0代表為正常使用者，is_blacklist為1代表為黑名單使用者，簡況如下:
第1行表示id為1的是正常使用者;
第2行表示id為2的不是正常使用者，是黑名單使用者，如果發送大量郵件或者出現各種情況就會容易發送郵件失敗的使用者
。。。
第4行表示id為4的是正常使用者
現在讓你寫一個sql查詢，每一個日期裡面，正常使用者發送給正常使用者郵件失敗的機率是多少，結果保留到小數點後面3位(3位之後的四舍五入)，并且按照日期升序排序，上面例子查詢結果如下:
結果表示:
2020-01-11失敗的機率為0.500，因為email的第1條資料，發送的使用者id為2是黑名單使用者，是以不計入統計，正常使用者發正常使用者總共2次，但是失敗了1次，是以機率是0.500;
2020-01-12沒有失敗的情況，是以機率為0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，隻有1*1.0/2才會得到0.5，sqlite四舍五入的函數為round)
select e.date, round(
sum(case e.type when 'completed' then 0 else 1 end) * 1.0 / count(e.type),
3) as p
from email e, `user` u1, `user` u2
where e.send_id = u1.id
and e.receive_id = u2.id
and u1.is_blacklist = 0
and u2.is_blacklist = 0
group by e.date
order by e.date
/*
round函數用于四舍五入
round(x,d) ，x指要處理的數，d是指保留幾位小數
注：d可以是負數，這時是指定小數點左邊的d位整數位為0,同時小數位均為0；
round(x) ,其實就是round(x,0),也就是預設d為0；
*/
/*
case...when... then... else... end
*/
牛客每個人最近的登入日期（二）
牛客每天有很多人登入，請你統計一下牛客每個使用者最近登入是哪一天，用的是什麼裝置.
有一個登入(login)記錄表，簡況如下:
第1行表示id為2的使用者在2020-10-12使用了用戶端id為1的裝置登入了牛客網
。。。
第4行表示id為3的使用者在2020-10-13使用了用戶端id為2的裝置登入了牛客網
還有一個使用者(user)表，簡況如下:
還有一個用戶端(client)表，簡況如下:
請你寫出一個sql語句查詢每個使用者最近一天登入的日子，使用者的名字，以及使用者用的裝置的名字，并且查詢結果按照user的name升序排序，上面的例子查詢結果如下:
查詢結果表明:
fh最近的登入日期在2020-10-13，而且是使用pc登入的
wangchao最近的登入日期也是2020-10-13，而且是使用ios登入的
select u.name u_n, c.name c_n, l.date
from login l, `user` u, client c
where l.user_id = u.id
and l.client_id = c.id
and l.date = (select max(l1.date) from login l1 where l.user_id = l1.user_id)
order by u_n
牛客每個人最近的登入日期（三）
牛客每天有很多人登入，請你統計一下牛客新登入使用者的次日成功的留存率，
有一個登入(login)記錄表，簡況如下:
第1行表示id為2的使用者在2020-10-12使用了用戶端id為1的裝置第一次新登入了牛客網
。。。
第4行表示id為3的使用者在2020-10-12使用了用戶端id為2的裝置登入了牛客網
。。。
最後1行表示id為1的使用者在2020-10-14使用了用戶端id為2的裝置登入了牛客網
請你寫出一個sql語句查詢新登入使用者次日成功的留存率，即第1天登陸之後，第2天再次登陸的機率,儲存小數點後面3位(3位之後的四舍五入)，上面的例子查詢結果如下:
查詢結果表明:
id為1的使用者在2020-10-12第一次新登入了，在2020-10-13又登入了，算是成功的留存
id為2的使用者在2020-10-12第一次新登入了，在2020-10-13又登入了，算是成功的留存
id為3的使用者在2020-10-12第一次新登入了，在2020-10-13沒登入了，算是失敗的留存
id為4的使用者在2020-10-13第一次新登入了，在2020-10-14沒登入了，算是失敗的留存
固次日成功的留存率為 2/4=0.5
(sqlite裡查找某一天的後一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函數為round，sqlite 1/2得到的不是0.5，得到的是0，隻有1*1.0/2才會得到0.5
mysql裡查找某一天的後一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函數為round)
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)
牛客每個人最近的登入日期（四）
牛客每天有很多人登入，請你統計一下牛客每個日期登入新使用者個數，
有一個登入(login)記錄表，簡況如下:
第1行表示id為2的使用者在2020-10-12使用了用戶端id為1的裝置登入了牛客網，因為是第1次登入，是以是新使用者
。。。
第4行表示id為2的使用者在2020-10-13使用了用戶端id為2的裝置登入了牛客網，因為是第2次登入，是以是老使用者
。。
最後1行表示id為4的使用者在2020-10-15使用了用戶端id為1的裝置登入了牛客網，因為是第2次登入，是以是老使用者
請你寫出一個sql語句查詢每個日期登入新使用者個數，并且查詢結果按照日期升序排序，上面的例子查詢結果如下:
查詢結果表明:
2020-10-12，有3個新使用者(id為2，3，1)登入
2020-10-13，沒有新使用者登入
2020-10-14，有1個新使用者(id為4)登入
2020-10-15，沒有新使用者登入
select a.date, sum(case when tmp = 1 then 1 else 0 end) new
from
(select `date`, row_number() over (partition by user_id order by `date`) tmp from login) a
group by `date`
/*
ROW_NUMBER()：順序排序——1、2、3
RANK()：并列排序，跳過重複序号——1、1、3
DENSE_RANK()：并列排序，不跳過重複序号——1、1、2
partition by 與group by不同之處在于前者傳回的是分組裡的每一條資料，
并且可以對分組資料進行排序操作。
後者隻能傳回聚合之後的組的資料統計值的記錄。
*/
牛客每天有很多人登入，請你統計一下牛客每個使用者查詢刷題資訊，包括: 使用者的名字，以及截止到某天，累計總共通過了多少題。 不存在沒有登入卻刷題的情況，但是存在登入了沒刷題的情況，不會存在刷題表裡面，有送出代碼沒有通過的情況，但是會記錄在刷題表裡，隻不過通過數目是0。
有一個登入(login)記錄表，簡況如下:
第1行表示id為2的使用者在2020-10-12使用了用戶端id為1的裝置登入了牛客網
。。。
第5行表示id為3的使用者在2020-10-13使用了用戶端id為2的裝置登入了牛客網
有一個刷題（passing_number)表，簡況如下:
第1行表示id為2的使用者在2020-10-12通過了4個題目。
。。。
第3行表示id為1的使用者在2020-10-13送出了代碼但是沒有通過任何題目。
第4行表示id為4的使用者在2020-10-13通過了2個題目
還有一個使用者(user)表，簡況如下:
請你寫出一個sql語句查詢刷題資訊，包括: 使用者的名字，以及截止到某天，累計總共通過了多少題，并且查詢結果先按照日期升序排序，再按照姓名升序排序，有登入卻沒有刷題的哪一天的資料不需要輸出，上面的例子查詢結果如下:
查詢結果表明:
fh在2020-10-12為止，總共通過了4道題，輸出為4
wangchao在2020-10-12為止，總共通過了1道題，總計為1
tm在2020-10-12為止隻登陸了沒有刷題，故沒有顯示出來
tm在2020-10-13為止刷了題，但是卻沒有通過任何題目，總計為0
wangchao在2020-10-13通過2道，但是加上前面2020-10-12通過1道，故在2020-10-13為止總共通過了3道題，總計為3
select u.name u_n, `date`, sum(number) over (partition by u.id order by `date`) ps_num
from passing_number pn left join user u
on pn.user_id = u.id
order by `date`, u_n
牛客每次舉辦企業筆試的時候，企業一般都會有不同的語言崗位，比如C++工程師，JAVA工程師，Python工程師，每個使用者筆試完有不同的分數，現在有一個分數(grade)表簡化如下:
第1行表示使用者id為1的選擇了language_id為1崗位的最後考試完的分數為12000，
…
第7行表示使用者id為7的選擇了language_id為2崗位的最後考試完的分數為11000，
不同的語言崗位(language)表簡化如下:
請你找出每個崗位分數排名前2的使用者，得到的結果先按照language的name升序排序，再按照積分降序排序，最後按照grade的id升序排序，得到結果如下:
ROW_NUMBER()：順序排序——1、2、3
RANK()：并列排序，跳過重複序号——1、1、3
DENSE_RANK()：并列排序，不跳過重複序号——1、1、2
select g.id, l.name, g.score
from (
select *, dense_rank() over (partition by language_id order by score desc) as tmp
from grade
) g, `language` l
where g.language_id = l.id and g.tmp <= 2
order by l.name, g.score desc, g.id
考試分數（四）
牛客每次考試完，都會有一個成績表(grade)，如下:
第1行表示使用者id為1的使用者選擇了C++崗位并且考了11001分
。。。
第8行表示使用者id為8的使用者選擇了B語言崗位并且考了9999分
請你寫一個sql語句查詢各個崗位分數升序排列之後的中位數位置的範圍，并且按job升序排序，結果如下:
解釋:
第1行表示C++崗位的中位數位置範圍為[2,2]，也就是2。因為C++崗位總共3個人，是奇數，是以中位數位置為2是正确的(即位置為2的10000是中位數)
第2行表示Java崗位的中位數位置範圍為[1,2]。因為Java崗位總共2個人，是偶數，是以要知道中位數，需要知道2個位置的數字，而因為隻有2個人，是以中位數位置為[1,2]是正确的(即需要知道位置為1的12000與位置為2的13000才能計算出中位數為12500)
第3行表示前端崗位的中位數位置範圍為[2,2]，也就是2。因為B語言崗位總共3個人，是奇數，是以中位數位置為2是正确的(即位置為2的11000是中位數)
(注意: sqlite 1/2得到的不是0.5，得到的是0，隻有1*1.0/2才會得到0.5，sqlite四舍五入的函數為round，sqlite不支援floor函數，支援cast(x as integer) 函數，不支援if函數，支援case when …then …else …end函數)
//中位數
select job, round(count(id) / 2) `start`, round((count(id) + 1) / 2) `end`
from grade
group by job
order by job
MYSQL取整函數
ROUND(X) – 表示将值 X 四舍五入為整數，無小數位
ROUND(X,D) – 表示将值 X 四舍五入為小數點後 D 位的數值，D為小數點後小數位數。若要保留 X 值小數點左邊的 D 位，可将 D 設為負值。
FLOOR(X)表示向下取整，隻傳回值X的整數部分，小數部分舍棄。
CEILING(X) 表示向上取整。