leetcode easy部分 无锁免费版

175. Combine Two Tables

image.png

select p.FirstName as FirstName ,p.LastName as LastName, a.city as City, a.state as State
from person as p
left join address as a
using(personid);

176.Second Highest Salary

176

SELECT 
(SELECT DISTINCT Salary FROM Employee 
 ORDER BY Salary DESC LIMIT 1 OFFSET 1) 
AS SecondHighestSalary;

select max(Salary) as SecondHighestSalary
from Employee
where Salary not in (select max(salary) from Employee)

mysql不支持top用法： limit的速度比第二个快
limit用法

181.Employees Earning More Than Their Managers

181

select e1.name as employee
from employee e1 
 join employee e2 
on e1.ManagerId=e2.id
where e1.salary>e2.salary;

SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary
;

用join更快

182.Duplicate Emails

182

select email
from person
group by email
having count(email) >1;

183.Customers Who Never Order

183

SELECT Name AS Customers FROM Customers WHERE Id NOT IN
(SELECT CustomerId FROM Orders);

select name as Customers
From customers left join orders on (customers.id = orders.customerid)
where orders.customerid is null;

196. Delete Duplicate Emails

196

DELETE p1 FROM Person AS p1 
JOIN Person AS p2 ON p1.Email = p2.Email AND p1.Id > p2.Id;

197.Rising Temperature

197

SELECT t.Id FROM Weather AS t, Weather AS y
WHERE DATEDIFF(t.RecordDate, y.RecordDate) = 1 AND t.Temperature > y.Temperature;

595. Big Countries

595

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

596. Classes More Than 5 Students

596

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

注意distinct

620. Not Boring Movies

620

select *
from cinema
where mod(id,2)=1 and description not like 'boring'
order by rating desc;

627.Swap Salary

627

update salary
case sex when 'm' then 'f' else 'm' end;

UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;

>注意update语法！！！！！