游戏玩法分析1

在本题中，如何在group中取第一次登陆日期将是难点所在。

为了解决这点我们可以用上聚合函数min()去做到，如果不用的话，将会导致取到顺序的第一个登陆日期，而不是我们期待的第一次登陆日期，

SELECTA.player_id,MIN(A.event_date) AS first_login
FROMActivity A
GROUP BYA.player_id;

https://leetcode.cn/problems/game-play-analysis-i/description/

分数排名

如何输出相应的rank？

• 以大于本分数的数量作为rank

  SELECTS1.score,(SELECTCOUNT(DISTINCT S2.score)FROMScores S2WHERES2.score >= S1.score) AS 'rank'
  FROMScores S1
  ORDER BYS1.score DESC;
  -- 此外值得注意的是rank要用上引号，否则会报错
  

• 采用DENSE_RANK窗口函数

  SELECTS.score,DENSE_RANK() OVER (ORDER BYS.score DESC) AS 'rank'
  FROMScores S;
  

• 与方法1思想类似，也是统计大于等于分数的个数，只不过是采用同表连接

  SELECTS.score,COUNT(DISTINCT T.score) AS 'rank'
  FROMScores SINNER JOIN Scores T ON S.score <= T.score
  GROUP BYS.id,S.score
  ORDER BYS.score DESC;
  

https://leetcode.cn/problems/rank-scores/description/

2016年的投资

两个条件就是两个子查询，我们只需要在子查询中找到所有满足条件的数据就可以了

SELECTROUND(SUM(insurance.TIV_2016), 2) AS TIV_2016
FROMinsurance
WHEREinsurance.TIV_2015 IN(SELECTTIV_2015FROMinsuranceGROUP BY TIV_2015HAVING COUNT(*) > 1)AND CONCAT(LAT, '.', LON) IN(SELECTCONCAT(LAT, '.', LON)FROMinsuranceGROUP BY CONCAT(LAT, '.', LON)HAVING COUNT(*) = 1)

https://leetcode.cn/problems/investments-in-2016/description/

连续出现的数字

本题关键在于如何判断数字是连续出现的，诀窍在于采用三表连接，表a、b、c的id分别是连续的，并且num相等

SELECT *
FROMLogs a,Logs b,Logs c
WHEREa.Id = b.Id - 1AND b.Id = c.Id - 1AND a.Num = b.NumAND b.Num = c.Num

https://leetcode.cn/problems/consecutive-numbers/description/

至少有5名直接下属的经理

• 子查询方法

  SELECT name FROM Employee WHERE id in (SELECT managerId FROM Employee GROUP BY managerId HAVING count(*)>=5)
  

• join

  select Manager.Name as Name
  from
  Employee as Manager join Employee as Report
  on Manager.Id = Report.ManagerId
  group by Manager.Id
  having count(Report.Id) >= 5
  

https://leetcode.cn/problems/managers-with-at-least-5-direct-reports/solutions/2366332/zhi-shao-you-5ming-zhi-jie-xia-shu-de-ji-syvu/