本文記錄了牛客網sql全部題目的答案與難題解析，部分題目包含多種解法，并且涵蓋了開窗函數等各種文法點的了解
标題中高亮的題目，是易錯題
牛客網刷題連結：牛客網sql線上練習
本文所有語句使用mysql8.0
參考教程資源：
mysql教程1
mysql教程2
1.where+子查詢
select * from employees
order by hire_date desc
limit 0,1;
select
*
from employees
where hire_date =
(
select
max(hire_date)
from employees
)
- limit m,n 從m+1處開始，傳回n行，m可以省略
- 使用子查詢的方法比較準确
2limit倒數第n個
select
*
from employees
where hire_date =
(
select
distinct hire_date
from employees
order by hire_date desc
limit 2,1
)
- 注意使用distinct進行去重
3簡單表連接配接
-
select b.*,a.dept_no from dept_manager a left join salaries b on a.emp_no = b.emp_no where a.to_date='9999-01-01' and b.to_date='9999-01-01' ORDER BY b.emp_no- 注意過濾掉已經離職的上司
4内連接配接inner join
select last_name
,first_name
,dept_no
from employees inner join dept_emp
on employees.emp_no = dept_emp.emp_no
- 注意使用内連接配接
- 注意連接配接時，大表（資料量多的表要放在前面）
5 左連接配接
select last_name
,first_name
,dept_no
from employees left join dept_emp
on employees.emp_no = dept_emp.emp_no
7簡單分組
-- 篩選變動次數超過15次的員工
select
emp_no
,count()
from salaries
group by emp_no
having count()>15
-- 篩選加薪次數超過15次的員工
select
a.emp_no
, count(*) as t
from salaries a
inner join salaries b
on a.emp_no=b.emp_no
where a.salaries < b.salaries
group by a.emp_no
having t>15
- 在第二個語句中，使用了自連接配接來，篩選出加薪的員工
- 當遇到一個同個字段進行比較時，應該使用自連接配接
8結果去重distinct或group by
--使用distinct進行去重
select
distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc
-- 使用groupby進行去重
select
salary
from salaries
where to_date = '9999-01-01'
group by salary
order by salary desc
- 當資料量較大時，建議使用groupby進行去重
- distinct會将select之後的所有字段合并進行去重
- group by 和distinct在去重的時候，都會包含null,将所有的null看作一個組
10null判斷
-- 使用join+is null
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
-- 使用子查詢
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
- 判斷非空時，應該使用
is null / is not null
!=null
- 連接配接的性能要高于子查詢，盡量使用連接配接而不是子查詢
11簡單表連接配接
select
a.emp_no
,b.emp_no
from dept_emp a inner join dept_manager b
on a.dept_no = b.dept_no
where a.emp_no != b.emp_no
and a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01'
12求分組字段最大值的資訊
- 方法1
-- 使用子查詢方法
select
d1.dept_no
,d1.emp_no
,s1.salary
from salaries s1 inner join dept_emp d1
on s1.emp_no = d1.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 salaries s2 inner join dept_emp d2
on s2.emp_no = d2.emp_no
AND d2.to_date='9999-01-01'
AND s2.to_date='9999-01-01'
AND d2.dept_no = d1.dept_no -- 利用内查詢擷取外查詢的部門編号
)
order by d1.dept_no asc
- 方法2
-- 使用開窗函數 select t.dept_no ,t.emp_no ,t.salary from ( select d.dept_no ,d.emp_no ,s.salary ,dense_rank() over (partition by d.dept_no order by s.salary desc) as rk from dept_emp d inner join salaries s on d.emp_no = s.emp_no where d.to_date='9999-01-01' and s.to_date='9999-01-01' ) t where t.rk = 1- 開窗函數使用方法連結
- dense_rank() 對每個dept_no部門中的partition分組資料進行排序
- row_number()，rank()，ntile(n),以及其他聚合函數
15奇數判斷
-
select *
from employees
where emp_no%2=1
and last_name != ‘Mary’
order by hire_date desc
```
- 不等于的幾種表示方法
- !=
- is not
- <>
- 奇數的表示方法
- x%2=1
- x&1 使用了位運算
16簡單分組
select
title
,avg(salary)as avg_salary
from salaries s inner join titles t
on s.emp_no = t.emp_no
where s.to_date='9999-01-01'
group by title
order by avg_salary
17排序取第n個值（order by + limit)
select
emp_no
,salary
from salaries
order by salary desc
limit 1,1
18使用子查詢或自連接配接進行排序
-- 使用子查詢
select
s.emp_no
,s.salary
,e.last_name
,e.first_name
from employees e
inner join salaries s
on e.emp_no = s.emp_no
where s.salary =
(
select
max(salary) -- 2 第二高工資
from salaries
where salary<>
(
select max(salary) -- 1查出最高工資
from salaries
where to_date='9999-01-01'
)
)
and s.to_date = '9999-01-01'
-- 使用自連接配接
select s.emp_no
, s.salary
, e.last_name
, e.first_name
from salaries s
join employees e
on s.emp_no = e.emp_no
where s.salary =
(
select s1.salary
from salaries s1 join salaries s2 -- 自連接配接查詢
on s1.salary <= s2.salary
group by s1.salary -- 當s1<=s2連結并以s1.salary分組時一個s1會對應多個s2
having count(distinct s2.salary) = 2 -- (去重之後的數量就是對應的名次)
)
and s.to_date = '9999-01-01'
- 當不使用order by 時，可以使用自連接配接或者多個子查詢
19 三表連接配接
select last_name
,first_name
,dept_name
from employees e
left join dept_emp d
on e.emp_no = d.emp_no
left join departments
on d.dept_no = departments.dept_no
- 多表連接配接時的執行順序： 連結連結
- 多表連接配接的時候，資料庫内部執行的順序，可能并不是我們寫的順序
21篩選某一字段的變化
select
a.emp_no
,b.salary - a.salary as growth
from salaries a
inner join employees e
on e.emp_no=a.emp_no
and e.hire_date = a.from_date
inner join salaries b
on a.emp_no = b.emp_no
where b.to_date= '9999-01-01'
order by growth
- 第一次join篩選出入職時的工資
- 第二次自連接配接篩選出，通過where篩選出目前的工資
22簡單連接配接+分組
select
de.dept_no
,de.dept_name
,count(*) as sum
from salaries s
join dept_emp d
on s.emp_no = d.emp_no
right join departments de
on d.dept_no = de.dept_no
group by de.dept_name,de.dept_no
order by de.dept_no
- 是用兩次表連接配接
- 使用groupby 進行分組
23視窗函數
select
emp_no
,salary
,dense_rank() over(order by salary desc) t_rank
from salaries
order by t_rank,emp_no
- 使用一個開窗函數來統計排名，相等的看作一個排名
24簡單連接配接
select
d.dept_no
,d.emp_no
,s.salary
from salaries s
join dept_emp d
on s.emp_no = d.emp_no
and s.to_date = '9999-01-01'
where s.emp_no not in
(
select
emp_no
from dept_manager de
where de.to_date = '9999-01-01'
)
25多表複用
select
emp_no
,manager_no
,emp_salary
,manager_salary
from
(
select
s1.emp_no as emp_no
,s1.salary as emp_salary
,dep1.dept_no
from salaries s1
join dept_emp dep1
on s1.emp_no = dep1.emp_no
where s1.emp_no not in
(
select emp_no
from dept_manager der
where der.to_date = '9999-01-01'
)
and s1.to_date = '9999-01-01'
)tem1-- 非上司的工資
join
(
select
s2.emp_no as manager_no
,s2.salary as manager_salary
,dep2.dept_no
from salaries s2
join dept_emp dep2
on dep2.emp_no = s2.emp_no
where s2.emp_no in
(
select emp_no
from dept_manager der
where der.to_date = '9999-01-01'
)
and s2.to_date = '9999-01-01'
) tem2 -- 部門上司的工資
on tem1.dept_no = tem2.dept_no -- 按部門進行連接配接
where emp_salary>manager_salary -- 篩選工資高低
select de.emp_no,
dm.emp_no as manager_no,
s1.salary as emp_salary,
s2.salary as 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.to_date='9999-01-01'
and s2.to_date='9999-01-01'
and s1.salary>s2.salary
- 使用多表複用可以将多個子查詢融合在一起，減少代碼量
26簡單分組
select de.dept_no
,dep.dept_name
,title
,count(*)
from dept_emp de
join titles ti
on ti.emp_no = de.emp_no
and de.to_date = '9999-01-01'
join departments dep
on de.dept_no = dep.dept_no
group by de.dept_no,title
order by de.dept_no
28虛表的使用
select
c.name
,count(f.film_id) as film_num
from film_category fc
join category c
on fc.category_id = c.category_id
join film f
on fc.film_id = f.film_id
where f.description like '%robot%'
and fc.category_id in
(
select category_id
from film_category
group by category_id
having count(*)>=5
)
group by c.name
- 使用子查詢來限制類别，保證類别的電影數量>=5
SELECT
c.name
, COUNT(fc.film_id)
FROM
(
select
category_id
, COUNT(film_id) AS category_num
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5
) AS cc
,film AS f
, film_category AS fc
, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
- 使用虛表來限制電影類别
29on和where
select
f.film_id
,title
from film f
left join film_category fc
on f.film_id = fc.film_id
where category_id is null
- 注意on和where的執行順序，把條件放在on上，會得出不正确的結果
- 當條件放在on上時，條件會和連接配接字段同時計算，進而導緻結果不正确
30簡單查詢
select
title
,description
from film f
join film_category fc
on f.film_id = fc.film_id
join category c
on c.category_id = fc.category_id
where name = 'Action'
32 concat字元串拼接
select
concat(last_name,' ' ,first_name) name
from employees
33 建立表
CREATE TABLE
IF NOT EXISTS actor ( -- 判斷是否已存在
actor_id smallint(5) NOT NULL PRIMARY KEY, -- 設定主鍵
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update date not null default current_timestamp -- 設定預設時間
)
- 目前日期：CURDATE(),CURRENT_DATE
- 目前時間：now()語句開始執行的時間,sysdate()語句執行到的時間
34批量插入
insert into actor (actor_id,
first_name,
last_name,
last_update)
values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
35ignore insert
insert ignore into actor(
actor_id
,first_name
,last_name
,last_update)
values
(
'3','ED','CHASE','2006-02-15 12:34:33'
)
- insert into:插入資料,如果主鍵重複，則報錯
- insert repalce:插入替換資料,如果存在主鍵或unique資料則替換資料
- insert ignore:如果存在主鍵或unique資料,則不進行insert。
36select insert
create table if not exists actor_name
(
first_name varchar(45) not null comment '名稱',
last_name varchar(45) not null comment '姓氏'
);
insert into actor_name(first_name
,last_name)
(select
first_name
,last_name
from actor )
- 注意同時執行多條語句時，要加“ ；”号
37建立索引
alter table actor
add unique index uniq_idx_firstname(first_name);
alter table actor
add index idx_lastname(last_name)
- 建立索引的方法與種類連結連結
-- 1通過create建立索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE] -- 索引資料類型，可以省略
ON table_name (column_name [(length)] [ASC | DESC],...) -- 可以添加多個字段，并指定排序方式
-- 索引的類型 PRIMARY KEY，KEY，UNIQUE或INDEX，當建立表時，若聲明了主鍵或者unique，會自動生成索引
-- 2 通過alter table建立索引
ALTER TABLE tbl_name ADD [PRIMARY KEY/UNIQUE INDEX indexname/fulltext indexname] (col_list);
-- 3删除索引
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;
38建立視圖
CREATE VIEW actor_name_view
AS
SELECT first_name as first_name_v
, last_name as last_name_v
FROM actor
- 建立索引的方法連結
CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name]
AS
[SELECT statement]
-- 使用條件：
-- SELECT語句可以在WHERE子句中包含子查詢，但FROM子句中的不能包含子查詢。
-- SELECT語句不能引用任何變量，包括局部變量，使用者變量和會話變量。
-- SELECT語句不能引用準備語句的參數。
39強制索引
SELECT
*
FROM salaries
FORCE INDEX(idx_emp_no )
WHERE emp_no = 10005
- 使用
FORCE INDEX(idx_name )
40增加一列
alter table actor
add column create_date datetime not null default '2020-10-1 00:00:00'
41建立觸發器
create trigger audit_log
after insert
on employees_test
for each row
begin
insert into audit(EMP_no,NAME)
values(NEW.ID,NEW.NAME);-- 注意加分号，表示語句的結束
end
- 觸發器建立：連結
- 在觸發語句中，使用new指代插入、更新後的行
- old代表更新前或者删除前的行
42條件删除
delete
from titles_test
where id not in
(
select id
from
(
select min(id) as id
from titles_test
group by emp_no
) as a
)
- 注意，在删除或者跟更新時，條件中的子查詢不能使用更新或者删除的表本身，必須使用别名的方式，将子查詢存儲為新表
-
DELETE FROM customers WHERE country = 'France' ORDER BY creditLimit LIMIT 5; - 使用order by 和limit也可以限制删除的範圍
- 當删除表中全部資料時，使用
truncate table table_name
43更新表
update titles_test
set to_date = null,
from_date='2001-01-01'
where to_date = '9999-01-01'
44 更新表（使用replace函數）
UPDATE titles_test
SET emp_no =
REPLACE(emp_no, 10001, 10005)
WHERE id = 5
- replace函數的使用連接配接
replace into titles_test
select
5
, 10005
, title
, from_date
, to_date
from titles_test
where id = 5;
- replace字句的三種使用方法
- 類似與insert
REPLACE INTO table_name(column_list)
VALUES(value_list)
- 類似于update
REPLACE INTO table
SET column1 = value1,
column2 = value2
- insert select
REPLACE INTO cities(name,population)
SELECT name,population FROM cities
WHERE id = 1
45alter的使用
alter table titles_test
rename to titles_2017
- alter的常用方法：
- ALTER TABLE 表名 ADD 列名/索引/主鍵/外鍵等；
- ALTER TABLE 表名 DROP 列名/索引/主鍵/外鍵等；
- ALTER TABLE 表名 ALTER 僅用來改變某列的預設值；
- ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
- ALTER TABLE 表名 RENAME TO/AS 新表名;
- ALTER TABLE 表名 MODIFY 列的定義但不改變列名；
- ALTER TABLE 表名 CHANGE 列名和定義都可以改變。
46 外鍵
alter table audit
add foreign key fk (emp_no)
references employees_test(id)
外鍵簡介：https://www.yiibai.com/mysql/foreign-key.html
外鍵限制文法：
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
action可以是
CASCADE
建立級聯删除或者級聯更新
action是
RESTRICT
或者
no action
時，不允許删除或者更新
action是
SET NULL
時，删除或更新父表時，子表被設定為null
48條件更新
update salaries
set
salary=1.1*salary
where
to_date = '9999-01-01'
and emp_no in
(
select emp_no
from emp_bonus
)
50字元串拼接
select concat(last_name,'\'',first_name)
from employees
51字元串長度與替換
select
char_length('A,10,B')- char_length(
replace('10,A,B',',','')
);
- 使用length統計字元串長度時，統計的是位元組長度，當編碼不同時，容易出錯，使用char_length更加準确
52截取字元串
select first_name
from employees
order by right(first_name,2)
- 常見的字元串函數
- 字元串處理函數
- 大小寫；
Lower(列名）
Upper(列名）
- 字元串定位
POSITION(字元 IN 列名) mysql
PATINDEX(’%s1%’, s2) sqlserver
- 合并
- 替換
- 取左
- 比對單引号等特殊字元
- 提取字元串
53分組字元串拼接
select
dept_no
,group_concat(emp_no separator ',')
from dept_emp
group by dept_no
order by dept_no
- group_concat函數使用方法：連結
- 使用文法：
-
按照orderby的順序，用sep分隔符，連接配接每個group中的字元串GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep);
54剔除最大最小值
select
avg(salary) as 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'
)
- 使用聚合函數
SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary
FROM salaries
where to_date = '9999-01-01';
55 分頁顯示
select
*
from
employees
order by emp_no
limit 5,5
- limit 每頁顯示數量*(第n頁 -1)，每頁顯示數量
56多表連接配接
select
distinct
e.emp_no
,d.dept_no
,btype
,received
from employees e
inner join dept_emp d
on e.emp_no = d.emp_no
left join emp_bonus b
on d.emp_no = b.emp_no
57exists
select
*
from employees e
where
not exists
(
select *
from dept_emp d
where d.emp_no = e.emp_no
)
- exists和in通常可以實作相同的功能
- 當從表（子查詢的表）很大時，通常使用in
59case語句
select
b.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 bonus
from emp_bonus b
left join employees e
on b.emp_no = e.emp_no
join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
- case文法
-
CASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ... ELSE commands END CASE
60累計和
select emp_no
,salary
,sum(salary) over(order by emp_no) as running_total
from salaries
where to_date = '9999-01-01'
- 累計和求法：
sum(xx) over (order by xx)
61開窗函數排序
SELECT
e.first_name
FROM employees e JOIN
(
SELECT
first_name
, ROW_NUMBER() OVER(ORDER BY first_name ASC) AS r_num
FROM employees
) AS t
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;
62分組篩選
select
number
from
grade
group by number
having count(*)>2
63開窗函數排序
select
id
,number
,dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank,id
- 不要忘記over函數後的排序方式desc
64左連接配接
select
p.id
,name
,content
from person p
left join task t
on p.id = t.person_id
65case與avg求組内某個字段數值比例
select
e.date
, round(AVG(
case e.type when 'no_completed' then 1
else 0
end
)
,3
)as p
from email e
join `user` u1
on e.receive_id = u1.id
join `user` u2
on e.send_id = u2.id
where u1.is_blacklist = 0
and u2.is_blacklist = 0
group by e.date
order by e.date
66開窗函數與分組
select
user_id
,max(`date`) as d
from login
group by user_id
order by user_id
- 使用開窗函數
select distinct
user_id,
last_value(date)
over(partition by user_id -- 按照user_id進行分組
order by date -- 組内按照date進行排序
rows between current row and unbounded following -- 計算時，從目前行到組内最後一行
) as d
from login;
- 開窗函數簡介
- 開窗函數使用
window_function_name(expression)
OVER (
[partition_defintion]
[order_definition]
[frame_definition]
)
frame_definition就是在partition内，取每一行的時候，在組内計算時，選擇的計算範圍
67開窗函數求最晚日期
/*開窗函數分組求最大時間，然後用子查詢篩選*/
select n.un u_n, n.cn c_n,n.d
from(
select u.name un
, c.name cn
, l.date,
(max(l.date) over(partition by l.user_id)) d
from login l,user u,client c
where l.user_id=u.id
and c.id=l.client_id
) n
where n.date=n.d
order by u_n;
68求初始注冊日留存率
- 使用開窗函數
select
round(
(
select
count(*) -- 通過where找到第二天仍然登陸的使用者記錄數
from
(
select -- 1找到初始登陸日期
user_id
,l.date
,min(date) over(partition by user_id) as first_date
from login l
) as temp1
where temp1.date = date_add(first_date,interval 1 day)
)
/
(
select -- 統計使用者總數
count(distinct user_id)
from login
)
,3)
as p
- 使用where子查詢
SELECT
ROUND(
COUNT(DISTINCT user_id)*1.0
/( -- 總使用者量
SELECT
COUNT(DISTINCT user_id)
FROM login
)
, 3)
FROM login
WHERE (user_id, date) -- 篩選出所有第二天登陸的使用者
IN
( -- 如果第二天登陸的日期和id
SELECT user_id
, DATE_ADD(MIN(date),INTERVAL 1 DAY)
FROM login GROUP BY user_id
);
date_add函數的使用：連結
DATE_ADD(start_date, INTERVAL expr unit)
常用unit
second,minute,hour,day,month,year
69使用row_number 來統計每天新注冊人數
select
date
,sum(
case tem.rk
when tem.rk = 1 then 1
else 0
end
) as new
from
(
select
user_id
,date
,row_number() over(partition by user_id order by `date`) as rk
from login
) as tem
group by tem.date
70按日期統計留存率
select
log_today.date
,round(count(distinct log_nextday.user_id)-- 當天新使用者的數量
/
count(distinct log_today.user_id)-- 第二天重新登入的新使用者數量
,3) as p
from login as log_today
left join(-- 注意要使用左連接配接
select -- 1 将日期向前移動一天，進而進行表連接配接，判斷第二天留存率
user_id
,date_sub(date,interval 1 day) as date
from login
) as log_nextday
on log_today.date = log_nextday.date
and log_today.user_id = log_nextday.user_id
where (log_today.user_id,log_today.date) -- 篩選每天登陸的新使用者，就是這裡把沒有新使用者登陸的天給篩選掉了，是以需要union
in (
select user_id
,min(date)
from login
group by user_id
)
group by log_today.date
union -- 将沒有新使用者的登陸的天，補充留存率為零
select date
,0.000 as p
from login
where date not in
(
select
min(date)
from login
group by user_id
)
order by date
71按日期和使用者的累計和
select
u.name as u_n
,login.date
,ps_num
from
(
select
user_id
,date
,sum(number) over(partition by user_id order by date) as ps_num
from passing_number
) as pas
join login
on login.user_id = pas.user_id
and login.date = pas.date
join `user` u
on u.id = login.user_id
order by date,u_n
72普通分組求平均
select
job
,round(avg(score),3) as avg_score
from
grade
group by job
order by avg_score desc
73大于平均值篩選
select
g1.*
from
grade g1
join (
select
id,
avg(score) over(partition by job) avg_score
from grade
) g2
on g1.id = g2.id
where g1.score > g2.avg_score
order by id
74求分組最高的兩個值
select
g.id
,l.name
,g.score
from grade g
join `language` l
on g.language_id = l.id
join (
select
id
,dense_rank() over(partition by language_id order by score desc) as rk
from grade
) as tem
on tem.id = g.id
where tem.rk in (1,2)
order by name asc,score desc
75求中位數的位置
select
job
,floor(( count(*) + 1 )/ 2 ) AS `start`
,floor(( count(*) + 2 )/ 2 ) AS 'end'
from grade
group by job
order by job
- 取整函數
- 四舍五入round()
- 向上取整ceiling()
- 向下取整floor()
76自連接配接求中位數
select
g1.id
,g1.job
,g1.score
,g2.rk as t_rank
from grade g1
join (
select
id
,row_number() over(partition by job order by score desc) as rk-- 每條記錄的排序
,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每個job的記錄數量
from grade
) as g2
on g1.id = g2.id
where -- 判斷中位數
g2.rk = floor((g2.num + 1 )/ 2 )
or g2.rk = floor((g2.num + 2 )/ 2 )
order by id
er_id
order by date,u_n
## 72普通分組求平均
```mysql
select
job
,round(avg(score),3) as avg_score
from
grade
group by job
order by avg_score desc
73大于平均值篩選
select
g1.*
from
grade g1
join (
select
id,
avg(score) over(partition by job) avg_score
from grade
) g2
on g1.id = g2.id
where g1.score > g2.avg_score
order by id
74求分組最高的兩個值
select
g.id
,l.name
,g.score
from grade g
join `language` l
on g.language_id = l.id
join (
select
id
,dense_rank() over(partition by language_id order by score desc) as rk
from grade
) as tem
on tem.id = g.id
where tem.rk in (1,2)
order by name asc,score desc
75求中位數的位置
select
job
,floor(( count(*) + 1 )/ 2 ) AS `start`
,floor(( count(*) + 2 )/ 2 ) AS 'end'
from grade
group by job
order by job
- 取整函數
- 四舍五入round()
- 向上取整ceiling()
- 向下取整floor()
76自連接配接求中位數
select
g1.id
,g1.job
,g1.score
,g2.rk as t_rank
from grade g1
join (
select
id
,row_number() over(partition by job order by score desc) as rk-- 每條記錄的排序
,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每個job的記錄數量
from grade
) as g2
on g1.id = g2.id
where -- 判斷中位數
g2.rk = floor((g2.num + 1 )/ 2 )
or g2.rk = floor((g2.num + 2 )/ 2 )
order by id
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)