菱形继承

select min(case when continent='America' then name end) America, min(case when continent='Asia' then name end)Asia, min(case when continent='Europe' then name end)Europe from( select *,row_number() over(partition by continent or...

select round( pow(pow(p1.x-p2.x,2)+pow(p1.y-p2.y,2),1/2),2 ) shortest from point_2d p1 inner join point_2d p2 where p1.x<>p2.x or p1.y<>p2.y order by 1 asc limit 1

select * , case when x+y>z and y+z>x and x+z>y then 'Yes' else 'No' end triangle from triangle

select id, case when p_id is null then 'Root' when id in (select p_id from tree) then 'Inner' else 'Leaf' end typefrom tree

select name from salesperson s where sales_id not in ( select sales_id from orders where com_id=( select com_id from company c where name='RED' ) )

(求连续的几个序号都可以用lag和lead这种套路，新增列然后判断）select seat_id from (select * ,lag(free,1) over(order by seat_id) l1,head(free,1) order(order by seat_id) l2 from cinema) A where free=1 and (l1=1 or l2=1)...

本质上就是统计两列中相同数字的最大值，注意到limit 1表示取第一行limit x,y表示从第x+1行开始取y行select id1 id,count(*) num from( select requester_id id1,accepter_id id2 from request_accepted r1 union select accepter_id id1,requester_id id2 ..

select id,visit_date,people from( select *, lag(people,1) over(order by id)l1, lag(people,2)over(order by id) l2, lead(people,1) over(order by id)l3, lead(people,2) over(order by id)l4 from stadium ...

select round( ifnull( 1/(count( distinct f1.sender_id,f1.send_to_id) /count(distinct r1.requester_id,r1.accepter_id)),0),2) accept_rate from friendrequest f1 , reques...

select class from courses group by 1 having count(distinct student)>=5

select c.country_name, case when avg(w.weather_state)<=15 then "Cold" when avg(w.weather_state)<25 then "Warm" else "Hot" end weather_type from countries c inner join weather w on c.country_id =w.country_id .

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

select customer_number from orders group by 1 order by count(*) desclimit 0,1

select round(sum(tiv_2016),2) tiv_2016 from (select *,count(*) over(partition by tiv_2015) a1,count(*) over(partition by lat,lon) a2 from insurance) as a where a1>1 and a2=1

select name from customer where referee_id <>2 or isnull(referee_id)

select dept_name,count(student_id) student_number from department d left join student s on d.dept_id=s.dept_id group by dept_name order by 2 desc,1

select id,month,salary+ifnull(l1,0)+ifnull(l2,0) salary from (select *,lag(salary,1) over(partition by id order by month ) l1, lag(salary,2) over(partition by id order by month)l2, rank() over(partition by id order by month desc) r ...

select request_at "Day",round(avg(`status`!="completed"),2) "Cancellation Rate" from trips t join users u1 on u1.users_id=t.client_id and u1.banned="No" join users u2 on u2.users_id=t.driver_id and u2.banned=...

select w1.id from weather w1 join weather w2 on datediff(w1.recorddate,w2.recorddate)=1 and w1.temperature>w2.temperature

delete from person where id not in( select id from( select min(id) id from person group by email)t )

select d.name "Department",e.name "Employee",Salary from Employee e join Department d on e.Departmentid=d.id where (select count(distinct(e1.salary)) from Employee e1 where e.salary<e1.salary and e.Departmentid=e1.Depart...

select d.name "Department",e.name "Employee",salary "Salary" from employee e join Department d on e.departmentid=d.id where (e.departmentid,salary) in (select departmentid,max(salary) from employee group by departmentid)

select d.name "Department",e.name "Employee",Salary from Employee e join Department d on e.Departmentid=d.id where (select count(distinct(e1.salary)) from Employee e1 where e.salary<e1.salary and e.Departmentid=e1.Depart...

select d.name "Department",e.name "Employee",salary "Salary" from employee e join Department d on e.departmentid=d.id where (e.departmentid,salary) in (select departmentid,max(salary) from employee group by departmentid)

# Write your MySQL query statement belowselect Name Customers from Customers where Id not in(select c.Id from Customers c,Orders o where c.Id = o.CustomerId)

select distinct l1.Num as ConsecutiveNums from Logs l1,Logs l2,Logs l3 where l1.id=l2.id-1 and l2.id=l3.id-1 and l1.Num=l2.Num and l2.Num=l3.Num

# Write your MySQL query statement belowselect Email from Person group by Email having count(Email)>1

# Write your MySQL query statement belowselect l1.Name Employee from Employee l1,Employee l2 where l1.Salary>l2.Salary and l1.ManagerId=l2.Id

# Write your MySQL query statement belowselect Score ,Id as `Rank` from Scores order by Score desc

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN SET N:=N-1; RETURN ( # Write your MySQL query statement below. select ifnull( (select distinct Salary from Employee order by Salary desc ...

花重金氪了一个力扣会员 就为了刷上锁的vip题 数据库其实挺重要的以下是解题目录：175. 组合两个表176. 第二高的薪水

原题链接# Write your MySQL query statement belowselect ifnull( (select distinct Salary from Employee order by Salary desc limit 1 offset 1) ,null) as SecondHighestSalary

原题链接# Write your MySQL query statement below/* Write your T-SQL query statement below */select FirstName,LastName,City,State from Person left join Address on Person.PersonId=Address.PersonId