中等難度一共14題
1.574.?Winning Candidate
join問題 找出出現(xiàn)頻率最高的，然后連接表找出對應(yīng)的值; 如果不用subquery的In的話可以直接join select好的另一個表
select Name from Candidate join (select CandidateId from Vote group by CandidateId order by count(*) desc limit 1) as winner on Candidate.id=winner.CandidateId
如果想寫的更簡單一點(diǎn)，連join都不用：SELECT Name FROM Candidate WHERE Id = (SELECT CandidateId FROM Vote GROUP BY CandidateId ORDER BY COUNT(id) DESC LIMIT 1) 直接用=號
2.180. Consecutive Numbers 連續(xù)出現(xiàn)問題
select l1.Num as 'ConsecutiveNums' from Logs as l1, Logs as l2, Logs as l3 where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 and l1.Num=l2.Num and l2.Num=l3.Num
這題的trick在于光這樣寫是不夠的，還要加上distinct， 因?yàn)橹貜?fù)出現(xiàn)三次的三張表中也肯能重復(fù)出現(xiàn)比如如果都是1，那∩2次會出現(xiàn)多個1，所以一定要記得加上distinct
即?select distinct l1.Num as 'ConsecutiveNums'……
3.Nth Highest Salary第幾高問題，記得我們在初級題里做過第二高的問題,當(dāng)時有用orderby然后limit做，同理
解法1：CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int;
set m=N-1;
? RETURN (
? ? ? select distinct salary as 'getNthHighestSalary(N)' from Employee order by Salary desc limit m,1
);
END
解法2：
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS?
BEGIN SET@N = @N-1;
?RETURN(
ISNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC OFFSET @NROWS FETCH NEXT 1 ROWS ONLY),NULL) );
END
這里我們學(xué)習(xí)一下sql中如何寫函數(shù)，以及過程中的變量怎么設(shè)：
{首先，mysql變量的種類：
用戶變量：以"@"開始，形式為"@變量名"。用戶變量跟mysql客戶端是綁定的，設(shè)置的變量，只對當(dāng)前用戶使用的客戶端生效
全局變量：定義時，以如下兩種形式出現(xiàn)，set GLOBAL 變量名? 或者? set @@global.變量名，對所有客戶端生效。只有具有super權(quán)限才可以設(shè)置全局變量
會話變量：只對連接的客戶端有效。
局部變量：作用范圍在begin到end語句塊之間。在該語句塊里設(shè)置的變量。declare語句專門用于定義局部變量。set語句是設(shè)置不同類型的變量，包括會話變量和全局變量。
局部變量與用戶變量的區(qū)分在于兩點(diǎn):1.用戶變量是以"@"開頭的。局部變量沒有這個符號。2.定義變量不同。用戶變量使用set語句，局部變量使用declare語句定義 3.作用范圍。局部變量只在begin-end語句塊之間有效。在begin-end語句塊運(yùn)行完之后，局部變量就消失了。
所以，最后它們之間的層次關(guān)系是：變量包括局部變量和用戶變量。用戶變量包括會話變量和全局變量。}
怎么用sql寫函數(shù)呢？標(biāo)準(zhǔn)模式：
Create function 函數(shù)名（參數(shù)）
Returns 返回值數(shù)據(jù)類型
[with {Encryption | Schemabinding }]
[as]
begin
SQL語句(必須有return 變量或值)
End
http://www.cnblogs.com/lideng/archive/2013/04/15/3022418.html 這個博客說sql函數(shù)的建立和調(diào)用說的挺好的
4.178.?Rank Scores 排序并添加序號問題: 思路是 先去重 排序， 再和原表join
https://blog.csdn.net/pashine/article/details/78919055 這篇文章講的很清晰
select Scores.Score, s1.Rank from Scores left join (select Score, (@i:=@i+1)as rank from (select distinct Score from Scores) as s, (select @i:=0)as xh order by Score desc) as s1 on s1.Score=Scores.Score order by Score desc
5.184.?Department Highest Salary分組求每組最大值問題 用group by 和In 而且先排序再group的話會自動取到第一條（只用group的話只會取到第一條）
select d.Name as Department, e.name as Employee, max(Salary) as Salary from Employee as e join Department as d on e.DepartmentId=d.Id group by Department
或者
SELECT
? ? Department.name AS 'Department',
? ? Employee.name AS 'Employee',
? ? Salary
FROM
? ? Employee
? ? ? ? JOIN
? ? Department ON Employee.DepartmentId = Department.Id
WHERE
? ? (Employee.DepartmentId , Salary) IN
? ? (? SELECT
? ? ? ? ? ? DepartmentId, MAX(Salary)
? ? ? ? FROM
? ? ? ? ? ? Employee
? ? ? ? GROUP BY DepartmentId
? ? )
;記得用兩個變量in的時候要加括號！！
6.614.?Second Degree Follower 求二度聯(lián)系人 自join的問題
可以這么做 用in
select followee as follower, count(*) as num from follow where followee in (select follower from follow where follower in (select followee from follow)) group by followee
或者 自join
select second_followee as 'follower', count(*) from (select f1.follower as second_followee, f2.follower as second_follower from follow f1, follow f2 where f1.follower=f2.followee) as f group by follower
7.578.?Get Highest Answer Rate Question 善用order by 將query寫的簡潔一些
注意null是不計(jì)算在count以內(nèi)的 善用if 函數(shù) if(條件，條件為真值，條件不為真值）
簡潔版：SELECT
? ? question_id AS 'survey_log'
FROM
? ? survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC
LIMIT 1;
累贅版：select question_id as survey_log from (select question_id, 2*sum(answer_var)/count(*) as answer_rate from (select question_id, case when answer_id is not null then 1
? ? else 0
? ? end as answer_var from survey_log) as s group by question_id order by answer_rate desc limit 1) as s1
8.580.?Count Student Number in Departments 又是一個分組計(jì)數(shù)+join
一個是注意join的時候用left join， 一個是注意不要把null count 進(jìn)去所以不能用count(*),而是count 某一列,還有注意不要忘了order
select dept_name, count(student_name)as student_number from (select dept_name, student_name from department as d left join student as s on s.dept_id=d.dept_id)as ds group by dept_name order by student_number desc, dept_name
9.585.?Investments in 2016
10.602.?Friend Requests II: Who Has the Most Friends 這題用union
11.
12.
13.
14.
