本文共 18254 字，大约阅读时间需要 60 分钟。

1.每位学生的最高成绩

select a.student_id ,a.course_id ,a.grade from (select *,    dense_rank() over (partition by student_id  order by grade desc,course_id ) as dese_rankfrom Enrollments ) as awhere a.dese_rank = 1order by student_id 

2.每日新用户统计

select activity_date as login_date, count(user_id) as user_countfrom (    select user_id, min(activity_date) as activity_date    from Traffic    where activity = "login"    group by user_id) as tgroup by login_datehaving datediff("2019-06-30", login_date) <= 90

3.小众书籍

select a.book_id ,a.namefrom (select *from Books where datediff('2019-06-23',available_from)>30) as awhere a.book_id not in (    select book_id    from Orders     where dispatch_date between '2018-06-23' and '2019-06-23'    group by book_id     having sum(quantity) >= 10) 

4.游戏玩法分析 V

select A.install_dt ,count(A.player_id) as installs,round(count(AA.player_id)/count(A.player_id),2) as Day1_retention from (	select player_id,min(event_date) as install_dt 	from Activity	group by player_id) as Aleft join Activity as AA on (AA.event_date = DATE_ADD(A.install_dt ,INTERVAL 1 DAY) and AA.player_id = A.player_id)group by A.install_dt 

5. 查询活跃业务

select a.business_id from (select *,    avg(occurences) over(partition by event_type) as meanfrom Events ) as awhere a.occurences >a.meangroup by a.business_id having count(distinct a.event_type) >= 2

6.用户购买平台

这题没做的出来

参考大神代码

https://leetcode-cn.com/problems/user-purchase-platform/solution/xiang-xi-bu-zou-jie-da-by-kobe24o-2/

select    spend_date, platform,    ifnull(sum(total_am),0) total_amount,    ifnull(sum(total_u),0) total_usersfrom(    select p.spend_date, p.platform, t.total_am, t.total_u    from    (        select distinct spend_date, "desktop" platform from Spending        union        select distinct spend_date, "mobile" platform from Spending        union        select distinct spend_date, "both" platform from Spending    ) p    left join    (        select spend_date,             if(count(distinct platform)=1, platform, 'both') plat,            sum(amount) total_am,            count(distinct user_id) total_u        from Spending        group by spend_date, user_id    ) t    on p.platform = t.plat and p.spend_date = t.spend_date) tempgroup by spend_date, platform

7.报告的记录 II

select round(ifnull(avg(daily_remove)*100,0),2) average_daily_percentfrom(    select action_date,count(distinct r.post_id)/count(distinct a.post_id) daily_remove    from actions a     left join removals r     on a.post_id = r.post_id    where extra = 'spam'    group by action_date) t1

8.查询近30天活跃用户数

select activity_date as day,count(distinct user_id) as active_users from Activitywhere datediff('2019-07-27',activity_date)<30group by activity_date 

9.过去30天的用户活动 II

select IFNULL(round(avg(a.cnt),2) ,0.0) as average_sessions_per_userfrom (select user_id,count(distinct session_id) as cntfrom Activity where datediff('2019-7-27',activity_date)<30group by user_id) as a

10.文章浏览 I

select distinct author_id as idfrom Views where author_id =viewer_id order by author_id

11.文章浏览 II

select distinct viewer_id as idfrom Views group by viewer_id ,view_date  having count(distinct article_id)>=2

12.市场分析 I

select a.user_id as buyer_id  ,a.join_date ,ifnull(b.cnt,0) as  orders_in_2019 from Users as aleft join (select buyer_id ,count(order_id) cntfrom Orders where year(order_date)='2019'group by buyer_id ) as bon a.user_id=b.buyer_id

13.市场分析 II

select u.user_id as seller_id,ifnull(case when i.item_brand = u.favorite_brand then 'yes' else 'no' end,'no') as '2nd_item_fav_brand'from users u left join(    select t1.seller_id,t1.item_id    from(        select seller_id,item_id,        row_number() over(partition by seller_id order by order_date) as rk        from orders    ) t1    where rk = 2) t2on u.user_id = t2.seller_idleft join items i on t2.item_id = i.item_id;

14.指定日期的产品价格

select t1.product_id ,    if(t2.new_price is not null,t2.new_price ,t1.price) as price from (select product_id ,10 as pricefrom Products  group by product_id) t1left join (select *from (select *,        rank() over (partition by product_id order by change_date desc) as rkfrom Products where change_date <= '2019-08-16') as awhere a.rk=1) as t2on t1.product_id = t2.product_id

15.即时食物配送 I

select round(count(delivery_id)*100/(select count(delivery_id ) from Delivery),2) as immediate_percentage from Deliverywhere order_date =customer_pref_delivery_date 

16.即时食物配送 2

select round(100*(select count(*)from (select *,        rank() over(partition by customer_id  order by order_date )  as rkfrom Delivery) as awhere a.rk = 1 and a.order_date =a.customer_pref_delivery_date )/(select count(*)from (select *,        rank() over(partition by customer_id  order by order_date )  as rkfrom Delivery) as awhere a.rk = 1 ),2) as immediate_percentage 

17.重新格式化部门表

通过group by id，该表中的数据被分为三组

此时在第一组中，有三条记录，也就是说有三个revenue，那么此时select id,
revenue就无法判定应该取哪一个revenue，所以这样的操作在标准SQL中是不允许的，只能通过聚合函数来处理。

聚合函数本质上就是常见编程语言里的reduce，也就是把一组数据处理成一个单一数据，


链接：https://leetcode-cn.com/problems/reformat-department-table/solution/group-byben-zhi-lun-by-loverxp-7mgy/
来源：力扣（LeetCode） 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

这里最关键的地方是使用GROUP BY将id分组，然后使用聚合函数SUM对同一id组的所有行进行检索

SUM相当于起了个遍历的作用，首先分组后变成这样

循环每个组的值 ， 当满足条件就聚合累加，不满足…

select id,    sum(case month when 'Jan' then revenue end) as Jan_Revenue,    sum(case month when 'Feb' then revenue end) as Feb_Revenue,    sum(case month when 'Mar' then revenue end) as Mar_Revenue,    sum(case month when 'Apr' then revenue end) as Apr_Revenue,    sum(case month when 'May' then revenue end) as May_Revenue,    sum(case month when 'Jun' then revenue end) as Jun_Revenue,    sum(case month when 'Jul' then revenue end) as Jul_Revenue,    sum(case month when 'Aug' then revenue end) as Aug_Revenue,    sum(case month when 'Sep' then revenue end) as Sep_Revenue,    sum(case month when 'Oct' then revenue end) as Oct_Revenue,    sum(case month when 'Nov' then revenue end) as Nov_Revenue,    sum(case month when 'Dec' then revenue end) as Dec_Revenuefrom Departmentgroup by id

18.每月交易 I

select date_format(trans_date,"%Y-%m") month        ,country        ,count(*) trans_count        ,count(if(state="approved",state,null)) approved_count        ,sum(amount) trans_total_amount        ,sum(if(state="approved",amount,0)) approved_total_amountfrom Transactionsgroup by date_format(trans_date,"%Y-%m")        ,country

SELECT LEFT(TRANS_DATE, 7) AS MONTH,        COUNTRY,        COUNT(ID) AS TRANS_COUNT,        COUNT(CASE WHEN STATE = 'approved' THEN ID END) AS APPROVED_COUNT,        SUM(AMOUNT) AS TRANS_TOTAL_AMOUNT,        IFNULL(SUM(CASE WHEN STATE = 'approved' THEN AMOUNT END), 0) AS APPROVED_TOTAL_AMOUNTFROM TRANSACTIONSGROUP BY COUNTRY,LEFT(TRANS_DATE, 7)

19.每月交易II

SELECT 	`month`, 	country, 	SUM(IF(state = 'approved', 1, 0)) approved_count, 	SUM(IF(state = 'approved', amount, 0)) approved_amount,	SUM(IF(state = 'chargeback', 1, 0)) chargeback_count,	SUM(IF(state = 'chargeback', amount, 0)) chargeback_amountFROM	(		SELECT 			id, country, state, amount, DATE_FORMAT(trans_date,'%Y-%m') `month` 		FROM 			Transactions t WHERE state LIKE 'approved'		UNION ALL		SELECT 			id, country, 'chargeback' state, amount, 	t1.`month`		FROM 			(				SELECT trans_id, DATE_FORMAT(trans_date, '%Y-%m') `month` FROM Chargebacks cb			) t1 			LEFT JOIN 			Transactions t			ON (t1.trans_id = t.id)		) t2 GROUP BY t2.`month`, t2.country;

20.查询结果的质量和占比

select     query_name,     round(avg(rating/position), 2) as quality,     round(sum(case when rating<3 then 1 else 0 end)/count(*)*100, 2) as poor_query_percentagefrom queriesgroup by query_name;

21. 最后一个能进入电梯的人

select a.person_name       from (select *,    sum(weight) over (order by turn) as smfrom  Queue) as awhere a.sm<=1000order by a.sm desclimit 1

22.锦标赛优胜者

select t4.group_id,t4.player_idfrom (select         t3.group_id,        t3.player_id,        rank() over (partition by t3.group_id  order by t3.score desc,t3.player_id) as rkfrom (select t2.player_id,t2.group_id ,t1.scorefrom (    select a.player,sum(a.score) as score    from (select first_player as player ,first_score  as score          from Matches           union all        select second_player ,second_score          from Matches ) as a    group by a.player    order by a.player) as t1left join Players as t2on t1.player = t2.player_id ) as t3) as t4where t4.rk=1

23.查询球队积分

select t1.team_id   ,t1.team_name ,           ifnull(t2.score1,0) as num_points    from Teams as t1left join (select host_team,sum(score) as score1from (select host_team ,    case when host_goals>guest_goals  then 3          when host_goals=guest_goals then 1    else 0 end as scorefrom Matches union allselect guest_team  ,    case when host_goals<guest_goals  then 3          when host_goals=guest_goals then 1    else 0 end as scorefrom Matches ) as tgroup by host_team) as t2on t1.team_id   = t2.host_teamorder by num_points desc,team_id 

24.报告系统状态的连续日期（时间与排序相减）

select state as period_state, min(dt) as start_date, max(dt) as end_date from  (    select *, subdate(dt, rank() over(partition by state order by dt)) as new_dt from    (        select 'failed' as state, fail_date as dt from Failed         where fail_date between '2019-01-01' and '2019-12-31'        union        select 'succeeded' as state, success_date as dt from Succeeded         where success_date between '2019-01-01' and '2019-12-31'    ) a ) b group by state, new_dt order by start_date

25.每个帖子的评论数

select a.sub_id as post_id ,ifnull(b.cnt,0) as number_of_comments from (select distinct sub_idfrom Submissions where parent_id is null) as aleft join (select parent_id,count(distinct sub_id) as cntfrom Submissions where parent_id is not nullgroup by parent_id) as bon a.sub_id = b.parent_idorder by post_id 

26.平均售价

select product_id ,        round(sum(gp)/sum(units),2) as average_price from (select a.product_id,a.price  * b.units  as gp,b.units from Prices as a,UnitsSold as bwhere a.product_id =b.product_id and a.start_date <=b.purchase_date and b.purchase_date <=a.end_date) as agroup by  product_id 

27.页面推荐

select distinct page_id  as  recommended_page from (select *from Friendship  where user1_id =1union allselect user2_id,user1_id from Friendship  where user2_id  =1) as t1left join Likes as t2on t1.user2_id = t2.user_id where page_id not in (select page_id from Likes where user_id =1) and page_id is not null

28.向公司CEO汇报工作的所有人

select a.employee_id as EMPLOYEE_IDfrom     Employees as a     left join     Employees as b on a.manager_id = b.employee_id    left join    Employees as c on b.manager_id = c.employee_id    left join    Employees as d on c.manager_id = d.employee_idwhere     a.employee_id <> 1     and    d.employee_id = 1; 

select employee_idfrom (select *from Employees where manager_id =1 and employee_id <> 1unionselect t1.employee_id ,t1.employee_name ,t2.manager_id from Employees as t1inner join (select *from Employees where manager_id =1 and employee_id <> 1) as t2on t1.manager_id  = t2.employee_idunionselect t3.employee_id ,t3.employee_name ,t4.manager_id from Employees as t3inner join (select t1.employee_id ,t1.employee_name ,t2.manager_id from Employees as t1inner join (select *from Employees where manager_id =1 and employee_id <> 1) as t2on t1.manager_id  = t2.employee_id) as t4on t3.manager_id  = t4.employee_id) as a

29.学生们参加各科测试的次数

select *from Students cross join Subjects

30.找到连续区间的开始和结束数字

select     min(log_id)  start_id ,    max(log_id) end_id from (select *,log_id-rk as cfrom (select *,    rank() over(order by log_id) as rkfrom Logs) as a) as bgroup by b.c

31.不同国家的天气类型

select  c.country_name  as country_name ,    case when avg(weather_state)<=15 then 'Cold'        when 25<=avg(weather_state) then 'Hot'        else 'Warm' end as weather_type from Weather as wleft join Countries as con w.country_id = c.country_idwhere w.day<'2019-12-01' and '2019-11-01'<=w.daygroup by w.country_id 

31.求团队人数

select     employee_id ,    count(*) over(partition by team_id)  as team_size  from Employee order by employee_id

32.不同性别每日分数总计

select     gender ,day,    sum(score_points) over (partition by gender order by day) as total from Scores

33.餐馆营业额变化增长

select visited_on,amount,average_amountfrom (select visited_on ,    rank() over(order by visited_on) as rnk,    sum(amount) over(rows between 6 preceding and current row) amount ,    round(avg(amount) over(rows between 6 preceding and current row),2) average_amountfrom (select customer_id , name ,visited_on, sum(amount)  as amountfrom Customer group by visited_on  ) as a) as bwhere b.rnk>=7

34.广告效果

select    ad_id,    case when Clicked+Viewed<>0 then round(Clicked*100/(Clicked+Viewed),2) else 0.00 end ctrfrom (select    ad_id  ,   ifnull(sum(case when action = 'Clicked' then 1 end),0) as Clicked,   ifnull(sum(case when action = 'Viewed' then 1 end),0) as Viewed,   ifnull(sum(case when action = 'Ignored' then 1 end),0) as Ignoredfrom Ads group by ad_id) as aorder by ctr desc,ad_id asc

参考

SELECT ad_id,    ROUND(IFNULL(SUM(action = 'Clicked') /        (SUM(action = 'Clicked') + SUM(action = 'Viewed')) * 100, 0), 2) AS ctrFROM AdsGROUP BY ad_idORDER BY ctr DESC, ad_id ASC

35.列出指定时间段内所有的下单产品

select p.product_name, t.unitfrom(select product_id,sum(unit) as unitfrom Orderswhere order_date between '2020-02-01' and '2020-02-29'group by product_idhaving sum(unit)>=100) as tleft join Products p on t.product_id=p.product_id;

36.每次访问的交易次数

37.电影评分

字典序（dictionary order），又称 字母序（alphabetical order），原意是表示英文单词在字典中的先后顺序，在计算机领域中扩展成两个任意字符串的大小关系。

select e.resultsfrom (select    b.name as results  ,count(*) as rlfrom Movie_Rating  as aleft join Users as bon a.user_id= b.user_idgroup by a.user_idorder by rl desc,b.namelimit 1unionselect     d.title as  results  ,avg(rating) as rl    from Movie_Rating as cleft join Movies as don c.movie_id =d.movie_idwhere  c.created_at between '2020-02-01' and '2020-02-29'group by c.movie_idorder by rl desc,d.titlelimit 1) as e

38.院系无效的学生

select id,namefrom Students  where department_id not in(    select id    from Departments )

39.活动参与者

select     activityfrom (select     *,    max(cnt) over() as ma,    min(cnt) over() as mifrom (select     activity ,count(distinct id) as cntfrom Friends group by activity ) as a) as bwhere cnt>mi and cnt < ma

40.顾客的可信联系人数量

select     t3.invoice_id ,t4.customer_name,t3.price ,t4.contacts_cnt,t4.trusted_contacts_cnt from Invoices  as t3left join(select t1.customer_id,t1.customer_name,t1.contacts_cnt,ifnull(t2.sa,0) as trusted_contacts_cnt from (select a.customer_id,a.customer_name,ifnull(b.contacts_cnt,0) as contacts_cntfrom Customers as aleft join (select user_id ,count(*) as contacts_cnt from Contacts group by  user_id  ) as b on a.customer_id = b.user_id) as t1left join (select     user_id ,sum(ca) as safrom (select     *,    case when contact_name in(select customer_name from Customers) then 1 else 0 end as cafrom Contacts) as cgroup by  user_id) as t2on t1.customer_id = t2.user_id) as t4on t3.user_id = t4.customer_idorder by t3.invoice_id 

41.获取最近第二次的活动

select     username,activity,startDate ,endDatefrom (select     *,    rank() over(partition by username order by endDate desc) as rk,    count(username) over(partition by username) as cntfrom UserActivity) as awhere cnt=1 or rk=2

select     username,activity,startDate ,endDatefrom (select     *,    rank() over(partition by username order by endDate desc) as rk,    count(username) over(partition by username) as cntfrom UserActivity) as awhere cnt>1 and rk=2union allselect *from UserActivitygroup by usernamehaving count(username) = 1 

42.使用唯一标识码替换员工ID

select b.unique_id ,a.namefrom Employees as aleft join EmployeeUNI  as bon a.id =b.id 

43.按年度列出销售总额

…

44.股票的资本损益

select t1.stock_name,sum(t2.price-t1.price) as capital_gain_loss from (select *from (select*,rank() over(partition by stock_name order by operation_day) as rkfrom Stocks) as awhere mod(a.rk,2)<>0) as t1inner join ( select *from (select*,rank() over(partition by stock_name order by operation_day) as rkfrom Stocks) as bwhere mod(b.rk,2)=0) as t2on t1.rk+1=t2.rk and t1.stock_name = t2.stock_namegroup by t1.stock_name

e…

select     stock_name,     sum(case         when operation='Buy' then -price        when operation='Sell' then price    end) as capital_gain_lossfrom stocksgroup by stock_name;

45.购买了产品 A 和产品 B 却没有购买产品 C 的顾客

select c.customer_id,customer_namefrom customers c join orders o on c.customer_id=o.customer_idgroup by 1having sum(product_name='A')>0 and sum(product_name='B')>0 and sum(product_name='C')=0 

46.排名靠前的旅行者

select     a.name,ifnull(travelled_distance ,0) as travelled_distancefrom Users as aleft join (select *,sum(distance) as travelled_distance from Ridesgroup by user_id) as bon a.id = b.user_idorder by  travelled_distance desc,name

47.查找成绩处于中游的学生

select *from Student where student_id  not in (select distinct student_idfrom (select  *,        rank() over(partition by exam_id order by score desc) as rkfrom Exam )as awhere a.rk = 1) and student_id not in(select distinct student_idfrom (select  *,        rank() over(partition by exam_id order by score) as rkfrom Exam )as awhere a.rk = 1) and student_id in (select distinct student_id  from Exam)

48.净现值查询

select    t1.id,t1.year,ifnull(t2.npv,0) as npvfrom Queries as t1left join NPV as t2on t1.id = t2.id and t1.year=t2.year

49.制作会话柱状图

select "[0-5>" as bin,count(case when duration/60<5 and duration/60>=0 then 1 end) as total from Sessionsunion allselect "[5-10>" as bin,count(case when duration/60<10 and duration/60>=5 then 1 end) as total from Sessionsunion allselect "[10-15>" as bin,count(case when duration/60<15 and duration/60>=10 then 1 end) as total from Sessionsunion allselect "15 or more" as bin,count(case when duration/60>=15 then 1 end) as total from Sessions

50. 计算布尔表达式的值

SELECT a.left_operand, operator, a.right_operand, CASE    WHEN b.value > c.value AND operator = '>' THEN 'true'    WHEN b.value < c.value AND operator = '<' THEN 'true'    WHEN b.value = c.value AND operator = '=' THEN 'true'    ELSE 'false'END AS valueFROM Expressions aINNER JOIN Variables bINNER JOIN Variables cON a.left_operand = b.name AND a.right_operand = c.name

51.苹果和桔子

select sale_date, lag_sale_date-sold_num as difffrom (select *,lag(sold_num ,1) over(partition by sale_date) as lag_sale_datefrom Sales) as awhere lag_sale_date is not nullorder by sale_date