20190528
一晾嘶、數(shù)據(jù)分析深入淺出
二熟吏、mysql必知必會(huì)
三距糖、leecode題庫(kù)
刷leecode數(shù)據(jù)庫(kù)題，涉及的函數(shù)牵寺。
1悍引、IFNULL函數(shù)：MySQL控制流函數(shù)之一，它接受兩個(gè)參數(shù)缸剪，如果不是NULL，則返回第一個(gè)參數(shù)东亦。 否則杏节，IFNULL函數(shù)返回第二個(gè)參數(shù)，兩個(gè)參數(shù)可以是文字值或表達(dá)式典阵。
如題：
編寫一個(gè) SQL 查詢奋渔，獲取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
Select
IFNULL(
(Select DISTINCT Salary
from Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary;
2壮啊、聚集函數(shù)不能直接用in匹配
問題：來(lái)刪除 Person 表中所有重復(fù)的電子郵箱嫉鲸，重復(fù)的郵箱里只保留 Id 最小的那個(gè)
錯(cuò)誤寫法：
delete from person where id not in (select min(id) id from person group by email);
正確寫法：
delete from person where id not in (select id from(select min(id) id from person group by email)t);
3、計(jì)算連續(xù)出現(xiàn)數(shù)字的問題
問題：編寫一個(gè) SQL 查詢歹啼，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字玄渗。
標(biāo)準(zhǔn)答案：
select distinct l.Num as 'ConsecutiveNums' from logs l,logs l1,logs l2 where l.Id=l1.Id-1 and l.Id=l2.Id-2 and l.Num=l1.Num and l.Num=l2.Num
還有種更復(fù)雜的聯(lián)結(jié)方式
select distinct(logs.num)as ConsecutiveNums from logs,(select (id-1)id,num from logs)a,(select (id-2)id,num from logs)b where logs.id=a.id and a.id=b.id and logs.num=a.num and a.num=b.num;
4、case+when的多級(jí)嵌套問題
問題：小美是一所中學(xué)的信息科技老師狸眼，她有一張 seat 座位表藤树，平時(shí)用來(lái)儲(chǔ)存學(xué)生名字和與他們相對(duì)應(yīng)的座位 id。其中縱列的 id 是連續(xù)遞增的拓萌。小美想改變相鄰倆學(xué)生的座位岁钓。你能不能幫她寫一個(gè) SQL query 來(lái)輸出小美想要的結(jié)果呢？
標(biāo)準(zhǔn)答案(直接不進(jìn)行多級(jí)嵌套，而是序列判斷)：
SELECT (CASE
WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id+1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id;
錯(cuò)誤答案（想使用多重嵌套屡限，但是嵌套格式不完整）品嚣，無(wú)法執(zhí)行：
select (case
when (id mod 2)=1 and id=(select count(*) from seat ) then id
else when (id mod 2)=1 then id+1
else id-1 end)
end
)id,student from seat order by id
錯(cuò)誤答案修正：
select (case
when (id mod 2)=1 and id=(select count(*) from seat ) then id
else (case when (id mod 2)=1 then id+1
else id-1 end)
end
)id,student from seat order by id
20190517
開始練習(xí)MySql經(jīng)典50，有一些之前沒涉及的函數(shù)钧大，做一些筆記記錄翰撑。
1、有這樣一組數(shù)據(jù)：('01','A'),('01','B'),('02','B'),('02','C')拓型，如何返回字母序列為｛A,B｝的序列號(hào)额嘿。
方法：限定數(shù)據(jù)域處在目標(biāo)閾中+數(shù)據(jù)域數(shù)目和目標(biāo)閾一致
create table AA(num varchar(6),aname varchar(6));
insert into AA(num,aname) values ('01','A'),('01','B'),('02','B'),('02','C');
select * from AA;
select AA.num from AA where AA.aname in ('A','B') group by AA.num having count(*)=2;
2、mysql的rank排序劣挫。
方法：
A：不可以用rank函數(shù)：
賦值排序
select student.*, (@a:=@a+1) from student, (select @a:=0) a;
按sname列排序(先內(nèi)部排序+賦值排序)
select stt.*,(@a:=@a+1) as rankname
from (select student.* from student order by sname)stt,(select @a:=0)
普通rank：
思路1：score 然后進(jìn)行用（@rank:=@rank+1）進(jìn)行排序册养，再group by score 取min(rank)，再聯(lián)立
思路2：a 表 left join a'压固，要求排序項(xiàng)a<a'球拦，再group by并進(jìn)行count(*)計(jì)數(shù)，再將非1的count值加上1 即為排名帐我。
dense_rank排序：
思路1：distinct score 然后進(jìn)行用（@rank:=@rank+1）進(jìn)行排序坎炼，再聯(lián)立
partition by排序：
思路1：先分組，再排序拦键，再union一起谣光；
思路2：自聯(lián)結(jié)a和a'，聯(lián)結(jié)條件是組類一致并且排序項(xiàng)a<=a'芬为，再group by 排序項(xiàng)萄金，并且count計(jì)算排序項(xiàng)的數(shù)目即為排名（但這個(gè)也只適用普通rank）。
B：可以用rank函數(shù)：
按sname列排序
select student.*,rank() over (order by Sname) st_rank from student;
按sid媚朦，sname分組氧敢，組內(nèi)按sname排序
select student.*,rank() over (partition by sid,sname order by Sname) st_rank from student;
C:涉及到排序，但是不用返回具體排名的問題：
若不能用rank函數(shù)询张，能用limit解決孙乖，盡量用limit解決，不用先計(jì)算排名份氧，如以下問題：
Employee 表包含所有員工信息唯袄，每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 department Id 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息蜗帜。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個(gè) SQL 查詢越妈，找出每個(gè)部門工資前三高的員工。
select d.name as Department,e.name as Employee,e.Salary
from employee e,department d
where e.departmentid=d.id
and ((e.departmentid,e.salary) in
(select * from(select distinct departmentid,salary from employee where departmentid=1 order by salary desc limit 3)a)
or
(e.departmentid,e.salary) in (select * from(select distinct departmentid,salary from employee where departmentid=2 order by salary desc limit 3)b)
)
order by Department asc,salary desc;
或者自聯(lián)結(jié)的方法（以下這種方法不用管部門到底有多少數(shù)目钮糖，因此不會(huì)有很多個(gè)or出現(xiàn)）
select d.name as Department,
e.name as Employee,
e.Salary
from employee e
inner join department d
on e.departmentid=d.id
left join employee as e1
on e.departmentid=e1.departmentid and e.salary<e1.salary
group by e.departmentid,e.id
having count(DISTINCT(e1.Salary))<3
order by e.departmentid asc,e.salary desc;
3梅掠、null值排序問題
null值排序默認(rèn)第一酌住，所以針對(duì)有null值的列進(jìn)行排序，需要先排序再聯(lián)結(jié)（left join 保留null值）阎抒。
先聯(lián)結(jié)再排序：
select *, rank()over(partition by cid01.cid order by cid01.score desc)rank01
from student
left join
(select * from sc where sc.cid='01')cid01 on student.sid=cid01.sid;
先排序再聯(lián)結(jié)：
select student.*,cid01.rank01
from student
left join
(select sc.sid,rank()over(partition by sc.cid order by sc.score desc)rank01 from sc where sc.cid='01')cid01
on student.sid=cid01.sid;
