力扣日记——数据库篇（2）

力扣日记——数据库篇（2）

181

解：

select e.Name as "Employee" from Employee e
WHERE e.Salary>(select Salary FROM Employee WHERE Id=e.ManagerID )

1179

主要是group by

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

注意一定要有聚合函数，比如sum

解：

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

这里月份是字符串要加引号

182

distinct 去重

select distinct e1.Email from Person e1,Person e2 where e1.Email=e2.Email and e2.Id!=e1.Id

196

考察DELETE用法

解：

delete e1 from Person e1,Person e2 WHERE e1.Id>e2.Id and e1.Email=e2.Email

596

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) as temp
WHERE
    num >= 5

先用一个group by和count把计数计出来，然后再从临时表挑满足的class

197

DATEDIFF,学到了

175

考察join

select FirstName,LastName,City,State from Person left join Address on Person.PersonId=Address.PersonId

595

select name,population,area from World where area>3000000 or population>25000000

太简单了，拯救了我疲惫的心

176

select(select distinct Salary from Employee order by Salary desc limit 1,1)as SecondHighestSalary 

先order by降序，然后用limit找第二高
要加distinct防止重复，为了出null可以在外面包一层select，里面作为临时表