SQL语句

sql join默认是inner join

182. Duplicate Emails

统计输出重复的Email

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

181. Employees Earning More Than Their Managers

一个表格,让员工比老板拿的多，

//Where:
select 
e1.Name as Employee
from Employee e1, Employee e2
where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
//Join:
select 
e1.Name as Employee
from Employee e1 join Employee e2
on e1.ManagerId = e2.Id and e1.Salary>e2.Salary

175. Combine Two Tables

针对两个表格，where相当于inner join，where用两个id进行限定

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

183. Customers Who Never Order

两个表格，一个不在另一个里面

SELECT Name as Customers from Customers
LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL;

select t1.Name as Customers 
from Customers as t1 
where t1.Id not in (select CustomerId  from Orders)

197. Rising Temperature

今天温度比昨天高

select t2.Id 
from Weather t1 , Weather t2  
where t2.Temperature > t1.Temperature and TO_DAYS(t2.DATE) - TO_DAYS(t1.DATE) = 1

176. Second Highest Salary

第二高工资

select (
  select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as SecondHighestSalary
//这句中的 distinct 表示salary不同， limit表示只显示1个，offset表示从第几个开始取，第三高后面跟2

196. Delete Duplicate Emails

删除重复的email，只保留最小的id

delete from Person where Id not in (select A.Id from (select min(Id) as Id from Person group by Email) A) 
delete p1 from Person p1, Person p2
where p1.email = p2.email and p1.Id > p2.Id;

178. Rank Scores

给出score值，给出rank值

select 
Score, 
 (select count(distinct Score) from Scores where Score >= s.Score ) Rank
 from Scores as s
 order by Score desc;
 //这个是更快的版本
 select 
Score, 
 (select count(*) from (select distinct Score s from Scores) tmp where s >= Score) Rank 
 from Scores as s
 order by Score desc;

180. Consecutive Numbers

连续出现的三个数

Select DISTINCT l1.Num 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

184. Department Highest Salary

两个表，确定某一个公寓里面住的人工资最高的那个

//根据Salary和DepartmentId得到确定某个人，
SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
FROM
    Employee E,
    (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T,
    Department D
WHERE E.DepartmentId = T.DepartmentId 
  AND E.Salary = T.max
  AND E.DepartmentId = D.id
 //下面的这个是套路型的方法
 SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
FROM Employee E
join Department D
on E.DepartmentId = D.Id
where 1 > (
    select count(distinct E2.Salary) 
    from Employee E2
    where E2.Salary > E.Salary
    and E2.DepartmentId = E.DepartmentId
);

177. Nth Highest Salary

创建了一个函数，用来实现第N个最大的数的返回

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
       SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

262. Trips and Users

计算被用户取消的概率

//这个涉及到一个函数，round，近似函数，case when then else end分支函数
select 
t.Request_at Day,
round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) as "Cancellation Rate"
from Trips t
inner join Users u
on t.Client_Id  = u.Users_Id  and u.Banned = "No"
where t.Request_at between  '2013-10-01' and '2013-10-03'
group by t.Request_at 

//这个就快多了，用联合查询还是慢的
SELECT Request_at as Day,
       ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
      AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;

185. Department Top Three Salaries

获取两个表其中某个公寓前三个数值

select d.Name as Department, e1.Name  as Employee , e1.Salary 
from Employee e1
inner join Department d
on e1.DepartmentId = d.Id
where 3 > (select count(distinct e2.salary) 
    from Employee e2
    where e2.Salary  > e1.Salary 
    and e1.DepartmentId =  e2.DepartmentId  
);