LeetCode刷题笔记-2020.8.14

摘要Flag

为了提高SQL的技能，准备记录一下SQL刷题的解题思路和技巧（虽然我是个鸽鸽大王，但是这次还是坚持一下吧：））

题目安排

暂定两周内

（每天3题简单+1题中等）

（每天2题简单+1题中等）-8.19

2020.8.14

1.重新格式化部门表-简单

需求：

解题思路1：

其实这就是将行转列，用sum (case when)实现即可，实际上要注意sum case when 里面的else条件要写null才符合题意；

代码实现1：

select 
id,
sum(case when month="Jan" then revenue else null end) as Jan_Revenue,
sum(case when month="Feb" then revenue else null end) as Feb_Revenue,
sum(case when month="Mar" then revenue else null end) as Mar_Revenue,
sum(case when month="Apr" then revenue else null end) as Apr_Revenue,
sum(case when month="May" then revenue else null end) as May_Revenue,
sum(case when month="Jun" then revenue else null end) as Jun_Revenue,
sum(case when month="Jul" then revenue else null end) as Jul_Revenue,
sum(case when month="Aug" then revenue else null end) as Aug_Revenue,
sum(case when month="Sep" then revenue else null end) as Sep_Revenue,
sum(case when month="Oct" then revenue else null end) as Oct_Revenue,
sum(case when month="Nov" then revenue else null end) as Nov_Revenue,
sum(case when month="Dec" then revenue else null end) as Dec_Revenue
from 
Department
group by id

在LC评论区有一条很有意思的参考资料，关于group by 的理解：https://blog.csdn.net/u014717572/article/details/80687042

解题思路2：

(id, month) 是联合主键 故分组后，每个分组中的月份都是唯一的
聚合函数 使用MIN 或 MAX都行

代码实现2：

SELECT
    id,
    MIN(IF(`month` = 'Jan', revenue, NULL)) AS Jan_Revenue,
    MIN(IF(`month` = 'Feb', revenue, NULL)) AS Feb_Revenue,
    MIN(IF(`month` = 'Mar', revenue, NULL)) AS Mar_Revenue,
    MIN(IF(`month` = 'Apr', revenue, NULL)) AS Apr_Revenue,
    MIN(IF(`month` = 'May', revenue, NULL)) AS May_Revenue,
    MIN(IF(`month` = 'Jun', revenue, NULL)) AS Jun_Revenue,
    MIN(IF(`month` = 'Jul', revenue, NULL)) AS Jul_Revenue,
    MIN(IF(`month` = 'Aug', revenue, NULL)) AS Aug_Revenue,
    MIN(IF(`month` = 'Sep', revenue, NULL)) AS Sep_Revenue,
    MIN(IF(`month` = 'Oct', revenue, NULL)) AS Oct_Revenue,
    MIN(IF(`month` = 'Nov', revenue, NULL)) AS Nov_Revenue,
    MIN(IF(`month` = 'Dec', revenue, NULL)) AS Dec_Revenue
FROM
    Department
GROUP BY id

解题思路3：

行转列，并求和，用pivot函数（Oracle）

代码实现3：

#Oracle-SQL
select  * from Department
pivot (SUM(REVENUE) for month in (
'Jan' as "Jan_Revenue",
'Feb' as "Feb_Revenue",
'Mar' as "Mar_Revenue",
'Apr' as "Apr_Revenue",
'May' as "May_Revenue",
'Jun' as "Jun_Revenue",
'Jul' as "Jul_Revenue",
'Aug' as "Aug_Revenue",
'Sep' as "Sep_Revenue",
'Oct' as "Oct_Revenue",
'Nov' as "Nov_Revenue",
'Dec' as "Dec_Revenue"

))

 

2.查找重复的电子邮箱-简单

需求：

解题思路1：

首先计算出每个邮箱出现的次数，再子查询，出现次数大于2的邮箱（记得子查询一定要group by否则会报错)

代码实现1：

select a.Email 
from 
(select Email,count(Id) as num from Person group by Email) as a 
where a.num>1

解题思路2：

向 GROUP BY 添加条件的一种更常用的方法是使用 HAVING 子句，该子句更为简单高效。

代码实现2：

select Email
from Person
group by Email
having count(Email) > 1;

 

3.分数排名-中等

需求：

 

解题思路：

这一题考察的就是排序窗口函数，因为需求排名结果是1123，所以用dense_rank()；

如果是1134，用rank()；

如果是1234，用row_number();

代码实现：

select Score,dense_rank() over(order by Score desc) `Rank`
from Scores

总结知识点：

1）pivot函数行转列（Oracle）；

2）联合主键唯一用min或者max分组进行行转列；

3）排序窗口函数：

　　a) dense_rank() 排序1123

命令格式： dense_rank() over(partition by col_list1 order by col_list2)

b) rank() 排序1134

　　命令格式：rank() over(partition by col_list1 order by col_list2)

c) row_number() 排序1234

命令格式：row_number() over(partition by col_list1 order by col_list2)

d) percent_rank() 排名百分比

命令格式:percent_rank() over(partition by col1[, col2…]

        order by col1 [asc|desc][, col2[asc|desc]…])

用途:计算一组数据中某行的相对排名参数说明；