xz的博客

https://leetcode-cn.com/problemset/database/?difficulty=%E5%9B%B0%E9%9A%BEwith sc as ( select player_id, sum(score) score from ( select first_player player_id,sum(first_score) score from Matches group by first_player union all s

会员只剩一天了，快点将觉得挺好的的题目保存一波select date_format(trans_date, '%Y-%m') month, country, sum(state = 'approved') approved_count, sum(if(state = 'approved', amount, 0)) approved_amount, sum(state = 'chargeback') chargeback_count, sum(if(st

Group Sold Products By The Datehttps://leetcode-cn.com/problems/group-sold-products-by-the-dateActivities table:±-----------------±----------------+| sell_date | product |±----------------±------------------+| 2020-05-30 | Headphone |.

每日新用户统计https://leetcode-cn.com/problems/new-users-daily-count/####第一种方法，将‘login’的过滤出来，组内排序，将结果即为sc####然后将排名为1的找出来并将日期在90天之外的过滤出去，然后分组统计。with sc as (select *,row_number() over(partition by user_id order by activity_date)'rankx' from Traffi.

学生地理信息报告https://leetcode-cn.com/problems/students-report-by-geography/# 先把学生组内排序，命名为sc，然后按照排名分组，每组按条件取值，#这个方法特别巧妙，一开始没想到。哎，还是太菜。with sc as ( select *, row_number() over(partition by continent order by name)rankx from student)select max(if.

569.员工薪水中位数https://leetcode-cn.com/problems/median-employee-salary/参考答案# 先组内排序，命名为sc，这里用了库函数，也可以自己写#这题的中位数是偶数个就是中间两个，奇数个就是中间一个，（题目要求）#然后看where子句，三个分别是每个公司和如果是奇数个就拿中间一个，偶数个就拿个数除以2的，偶数个就拿个数除以2加1的#然后根据公司和组内排名就把中无数拿出来了。with sc as (select *,row_nu

180，连续出现的数字https://leetcode-cn.com/problems/consecutive-numbers/####三表连接####这个没什么好说的，就有个问题，题目没有说明id是连续的，唯一的。虽然题目默认是这样。select distinct a.Num as "ConsecutiveNums" from Logs a join Logs b join Logs c on a.num = b.num and a.num = c.num and a.id = b.id

Sales by Day of the Weekhttps://leetcode-cn.com/problems/sales-by-day-of-the-week/翻译过来的意思就是说统计每个item_category每个星期的销量，结果如下所示：####这题感觉不难，就是一个计算日期为星期的函数####dayname可以直接将日期换成星期select item_category category, sum(if(dayname(b.order_date)='Monday'.

获取最近第二次的活动https://leetcode-cn.com/problems/get-the-second-most-recent-activity/####提供两种方法，一：####首先将只有一个人的拿出来，命名为sc这个肯定是答案的一部分。####其次就组内排序，做降序，取出排名为2的，####使用mysql的over()函数，或者自己两表合并来做组内排序，这里两种都给了出来，####命名为two，####讲下自己写组内排序，就是两表按照username来合并，然后按照某一.

按年度列出销售总额https://leetcode-cn.com/problems/total-sales-amount-by-year/with sc as ( select product_id,'2018' report_year ,sum(datediff( if(datediff('2018-12-31',period_end)>0,period_end,'2018-12-31') , if (datediff('2018-01-01',period_st.

部门工资前三高的所有员工https://leetcode-cn.com/problems/department-top-three-salaries/###这题想法挺简单的，###1，先组内排序叫做rankx，这里提供了两种组内排序，一个是mysql8.0之后提供的rank() over()函数，一种试不用系统的高级函数。###2，将部门名字连接到员工表，过滤rk<=3，并排序。###注意的是这里的前三的排名是指，1,2,2,3,4这种。前三可以是3个以上的人数。所以使用了dense.

查找成绩处于中游的的学生https://leetcode-cn.com/problems/find-the-quiet-students-in-all-exams/##1，首先将每门的最高分和最低分查出来，叫做max_min##2,将所有考的每门的最高和最低分查出来，叫做max_min_id##3，然后将student表和exam表内联。##4，过滤的条件是，学生的id没在刚刚查出来的表max_min_id里##5，最后排序。with max_min as ( select e.

力扣 1225https://leetcode-cn.com/problems/report-contiguous-dates/先给出整体的代码;# Write your MySQL query statement belowwith fx as (select fail_date datex ,'failed' flag from Failed where fail_date between '2019-01-01' and '2019-12-31'),sx as (sele

来源：力扣（LeetCode）链接：https://leetcode-cn.com/problems/number-of-transactions-per-visit题目如下：对于 transactions_count = 0, visits 中 (1, “2020-01-01”), (2, “2020-01-02”), (12, “2020-01-01”) 和 (19, “2020-01-03”) 没有进行交易，所以 visits_count = 4 。对于 transactions_cou