数据库相关算法题 V2

最新推荐文章于 2024-10-02 00:27:17 发布

动态一时爽，重构火葬场

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量140

点赞数

分类专栏： db algorithm 文章标签：数据库算法

本文链接：https://blog.csdn.net/iUcool/article/details/134761591

版权

db 同时被 2 个专栏收录

27 篇文章 0 订阅

订阅专栏

algorithm

20 篇文章 0 订阅

订阅专栏

游戏玩法分析1

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

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

SELECT
  A.player_id,
  MIN(A.event_date) AS first_login
FROM
  Activity A
GROUP BY
  A.player_id;

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

分数排名

如何输出相应的rank？

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

SELECT
  S1.score,
  (
    SELECT
      COUNT(DISTINCT S2.score)
    FROM
      Scores S2
    WHERE
      S2.score >= S1.score
  ) AS 'rank'
FROM
  Scores S1
ORDER BY
  S1.score DESC;
-- 此外值得注意的是rank要用上引号，否则会报错

采用DENSE_RANK窗口函数

SELECT
  S.score,
  DENSE_RANK() OVER (
    ORDER BY
      S.score DESC
  ) AS 'rank'
FROM
  Scores S;

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

SELECT
  S.score,
  COUNT(DISTINCT T.score) AS 'rank'
FROM
  Scores S
  INNER JOIN Scores T ON S.score <= T.score
GROUP BY
  S.id,
  S.score
ORDER BY
  S.score DESC;

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

2016年的投资

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

SELECT
    ROUND(SUM(insurance.TIV_2016), 2) AS TIV_2016
FROM
    insurance
WHERE
    insurance.TIV_2015 IN
    (
      SELECT
        TIV_2015
      FROM
        insurance
      GROUP BY TIV_2015
      HAVING COUNT(*) > 1
    )
    AND CONCAT(LAT, '.', LON) IN
    (
      SELECT
        CONCAT(LAT, '.', LON)
      FROM
        insurance
      GROUP BY CONCAT(LAT, '.', LON)
      HAVING COUNT(*) = 1
    )

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

连续出现的数字

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

SELECT *
FROM
    Logs a,
    Logs b,
    Logs c
WHERE
    a.Id = b.Id - 1
    AND b.Id = c.Id - 1
    AND a.Num = b.Num
    AND 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/