中等难度一共14题
1.574. Winning Candidate
join问题 找出出现频率最高的，然后连接表找出对应的值; 如果不用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
如果想写的更简单一点，连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 连续出现问题
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， 因为重复出现三次的三张表中也肯能重复出现比如如果都是1，那∩2次会出现多个1，所以一定要记得加上distinct
即 select distinct l1.Num as 'ConsecutiveNums'……
3.Nth Highest Salary第几高问题，记得我们在初级题里做过第二高的问题,当时有用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
这里我们学习一下sql中如何写函数，以及过程中的变量怎么设：
{首先，mysql变量的种类：
用户变量：以"@"开始，形式为"@变量名"。用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名，对所有客户端生效。只有具有super权限才可以设置全局变量
会话变量：只对连接的客户端有效。
局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量。
局部变量与用户变量的区分在于两点:1.用户变量是以"@"开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。
所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。}
怎么用sql写函数呢？标准模式：
Create function 函数名（参数）
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End
http://www.cnblogs.com/lideng/archive/2013/04/15/3022418.html 这个博客说sql函数的建立和调用说的挺好的
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 求二度联系人 自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是不计算在count以内的 善用if 函数 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 又是一个分组计数+join
一个是注意join的时候用left join， 一个是注意不要把null count 进去所以不能用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.
网友评论