![](https://img-blog.csdnimg.cn/20190918140012416.png?x-oss-process=image/resize,m_fixed,h_224,w_224)
Leetcode数据库Mysql-Medium
Leetcode数据库Mysql-Medium
Love 6
腾讯WXG在职后台小鹅
展开
-
Leetcode Mysql 1355. 活动参与者(DAY 20)
文章目录原题题目代码实现(首刷看了部分解 SOME ALL使用)原题题目代码实现(首刷看了部分解 SOME ALL使用)# Write your MySQL query statement belowSELECT activityFROM friendsGROUP BY activityHAVING COUNT(*) > SOME(SELECT COUNT(*) FROM friends GROUP BY activity)AND COUNT(*) <原创 2021-11-01 22:29:05 · 120 阅读 · 0 评论 -
Leetcode Mysql 1549. 每件商品的最新订单(DAY 20)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT product_name,t.product_id,order_id,order_dateFROM (SELECT product_id,order_id,order_date,DENSE_RANK() OVER(PARTITION BY product_id ORDER BY order_date DESC) AS rnk原创 2021-11-01 22:07:14 · 140 阅读 · 0 评论 -
Leetcode Mysql 612. 平面上的最近距离(DAY 19)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT ROUND(MIN(SQRT(POWER(t1.x - t2.x,2) + POWER(t1.y - t2.y,2))),2) AS shortestFROM Point2D AS t1 JOIN Point2D AS t2WHERE (t1.x,t1.y) <> (t2.x,t2.y)...原创 2021-11-01 14:39:41 · 102 阅读 · 0 评论 -
Leetcode Mysql 534. 游戏玩法分析 III(DAY 18)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT player_id,event_date,SUM(games_played) OVER(PARTITION BY player_id ORDER BY event_date) AS games_played_so_farFROM activity...原创 2021-10-31 11:50:18 · 1945 阅读 · 0 评论 -
Leetcode Mysql 1951. 查询具有最多共同关注者的所有两两结对组(DAY 17)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT user1_id,user2_idFROM(SELECT user1_id,user2_id,DENSE_RANK() OVER(ORDER BY cnt DESC) AS rnkFROM (SELECT r1.user_id AS user1_id,r2.user_id AS user2_id,r1.follower_id AS原创 2021-10-30 20:41:26 · 180 阅读 · 0 评论 -
Leetcode Mysql 626. 换座位(DAY 16)
文章目录原题题目代码实现(首刷大部分看解 妙)原题题目代码实现(首刷大部分看解 妙)# Write your MySQL query statement belowSELECT (CASE WHEN MOD(id,2) = 0 THEN id-1 WHEN id <> (SELECT max(id) FROM seat) THEN id+1 ELSE id END) id,studentF原创 2021-10-21 15:53:33 · 136 阅读 · 0 评论 -
Leetcode Mysql 1126. 查询活跃业务(DAY 16)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT business_id FROM events AS e1LEFT JOIN (SELECT event_type,SUM(occurences) / COUNT(*) AS event_type_avg FROM events GROUP BY event_type) AS e2 US原创 2021-10-21 15:22:34 · 147 阅读 · 0 评论 -
Leetcode Mysql 1709. 访问日期之间最大的空档期(DAY 14)
文章目录原题题目代码实现(首刷学习 Lead函数)原题题目代码实现(首刷学习 Lead函数)# Write your MySQL query statement belowSELECT user_id,MAX(DATEDIFF(IF(next_date is null,"2021-1-1",next_date),visit_date)) biggest_windowFROM (SELECT user_id,visit_date,LEAD(visit_date,1) OVER(PART原创 2021-10-15 12:29:49 · 103 阅读 · 0 评论 -
Leetcode Mysql 1747. 应该被禁止的Leetflex账户(DAY 14)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT DISTINCT l1.account_idFROM loginfo l1 JOIN loginfo l2 USING(account_id)WHERE l1.ip_address <> l2.ip_address AND l2.logout between l1.login and l1.logout原创 2021-10-15 12:13:06 · 100 阅读 · 0 评论 -
Leetcode Mysql 1468. 计算税后工资(DAY 13)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT s1.company_id,s1.employee_id,s1.employee_name,ROUND(s1.salary*s2.rating) AS salaryFROM salaries s1 LEFT JOIN (SELECT company_id,1 - if(MAX(salary) >= 100原创 2021-10-13 15:51:24 · 167 阅读 · 0 评论 -
Leetcode Mysql 1077. 项目员工 III(DAY 11)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT project_id,employee_idFROM (SELECT project_id,p.employee_id,DENSE_RANK() OVER(PARTITION BY project_id ORDER BY experience_years DESC) AS ranking FROM projec原创 2021-10-11 14:34:48 · 142 阅读 · 0 评论 -
Leetcode Mysql 1661. 每台机器的进程平均运行时间(DAY 11)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT machine_id,ROUND((SUM(if(activity_type = 'end',sum_time,0)) - SUM(if(activity_type = 'start',sum_time,0))) / t.count,3) AS processing_timeFROM (SELECT machine_id,ac原创 2021-10-11 14:13:33 · 141 阅读 · 0 评论 -
Leetcode Mysql 1565. 按月统计订单数与顾客数(DAY 10)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT month,order_count,customer_countFROM (SELECT DATE_FORMAT(order_date,"%Y-%m") AS month,COUNT(DISTINCT order_id) AS order_count,COUNT(DISTINCT customer_id) AS customer_原创 2021-10-10 12:27:55 · 206 阅读 · 0 评论 -
Leetcode Mysql 1715. 苹果和橘子的个数(DAY 10)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT SUM(b.apple_count+if(c.chest_id is NULL,0,c.apple_count)) AS apple_count,SUM(b.orange_count+if(c.orange_count is NULL,0,c.orange_count)) AS orange_countFROM Boxes AS b原创 2021-10-10 12:04:43 · 146 阅读 · 0 评论 -
Leetcode Mysql 1596. 每位顾客最经常订购的商品(DAY 8)
文章目录原题题目代码实现(首刷自解 效率巨慢)原题题目代码实现(首刷自解 效率巨慢)# Write your MySQL query statement belowSELECT t1.customer_id,t1.product_id,product_nameFROM (SELECT customer_id,product_id FROM orders o1 GROUP BY customer_id,product_id HAVING原创 2021-10-08 12:21:55 · 188 阅读 · 0 评论 -
Leetcode Mysql 586. 订单最多的客户(DAY 8)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT customer_numberFROM ordersGROUP BY customer_number HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM orders GROUP BY cust原创 2021-10-08 12:20:42 · 111 阅读 · 0 评论 -
Leetcode Mysql 1421. 净现值查询(DAY 8)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT q.id,q.year,if(n.npv is null,0,n.npv) npvFROM queries AS q LEFT JOIN npv AS n on q.id = n.id and q.year = n.year ...原创 2021-10-08 12:11:44 · 150 阅读 · 0 评论 -
Leetcode Mysql 1308. 不同性别每日分数总计(DAY 8)---- 学习窗口函数
文章目录原题题目代码实现(首刷 学习窗口函数 OVER)原题题目代码实现(首刷 学习窗口函数 OVER)# Write your MySQL query statement belowSELECT gender,day,SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS totalFROM scores...原创 2021-10-08 12:02:28 · 148 阅读 · 0 评论 -
Leetcode Mysql 1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客(DAY 7)
文章目录原题题目代码实现(首刷自解)代码实现(首刷优化)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECTDISTINCT c.customer_id, c.customer_nameFROM customers cJOIN orders oWHERE c.customer_id = o.customer_id and exists (SELECT * FRO原创 2021-10-04 17:52:10 · 187 阅读 · 0 评论 -
Leetcode Mysql 1783. 大满贯数量(DAY 4)
文章目录原题题目代码实现(首刷大部分看解 熟悉GROUP BY SUM带条件求和)原题题目代码实现(首刷大部分看解 熟悉GROUP BY SUM带条件求和)# Write your MySQL query statement belowSELECT p.player_id, p.player_name, SUM(if(Wimbledon = player_id,1,0)) + SUM(if(Fr_open = player_id,1,0)) + SUM(if(US原创 2021-10-01 22:10:39 · 129 阅读 · 0 评论 -
Leetcode Mysql 1699. 两人之间的通话次数(DAY 4)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT if(from_id < to_id,from_id,to_id) person1, if(from_id > to_id,from_id,to_id) person2, COUNT(*) call_count, SUM(duration) total_durationFROM原创 2021-10-01 11:24:55 · 161 阅读 · 0 评论 -
Leetcode Mysql 1270. 向公司CEO汇报工作的所有人(DAY 4)
文章目录原题题目代码实现(首刷绝大部分看解 理解Join 多表联合)原题题目代码实现(首刷绝大部分看解 理解Join 多表联合)# Write your MySQL query statement belowSELECT e1.employee_idFROM employees e1 join employees e2 join employees e3WHERE e1.employee_id <> 1 and e3.manager_id = 1 and e1.manage原创 2021-10-01 11:24:32 · 133 阅读 · 0 评论 -
Leetcode Mysql 1393. 股票的资本损益(DAY 2)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT s1.stock_name,(s1.total_sum - s2.total_sum) capital_gain_lossFROM (SELECT stock_name,SUM(price) total_sum FROM stocks where operation = "sell" GR原创 2021-09-29 12:34:35 · 152 阅读 · 0 评论 -
Leetcode Mysql 1445. 苹果和桔子(DAY 2)
文章目录原题题目代码实现(首刷自解)原题题目代码实现(首刷自解)# Write your MySQL query statement belowSELECT s1.sale_date,(s1.sold_num - s2.sold_num) diff FROM sales s1 INNER JOIN sales s2 ON s1.sale_date = s2.sale_dateWHERE s1.fruit = 'apples' and s2.fruit = 'oranges'ORDER原创 2021-09-29 11:54:38 · 107 阅读 · 0 评论