SQL语句

sql join默认是inner join

182. Duplicate Emails

统计输出重复的Email

select Email from Persongroup by Emailhaving count(*) > 1;

181. Employees Earning More Than Their Managers

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

//Where:select e1.Name as Employeefrom Employee e1, Employee e2where e1.ManagerId = e2.Id and e1.Salary > e2.Salary//Join:select e1.Name as Employeefrom Employee e1 join Employee e2on 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 CustomersLEFT JOIN OrdersON Customers.Id = Orders.CustomerIdWHERE 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 p2where 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 DWHERE 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 Ejoin Department Don E.DepartmentId = D.Idwhere 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 INTBEGINDECLARE 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 tinner join Users uon 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 TripsWHERE (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 e1inner join Department don e1.DepartmentId = d.Idwhere 3 > (select count(distinct e2.salary)     from Employee e2    where e2.Salary  > e1.Salary     and e1.DepartmentId =  e2.DepartmentId  );