[leetcode][SQL]ALL

#SQL-182. Duplicate Emails

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

#SQL-181. Employees Earning More Than Their Managers

Select a.Name as Employee from
Employee a , Employee b
where a.ManagerId=b.Id and a.Salary>b.Salary;

#也可以省略as， 也可 ‘,’ 变成join ，也可 and 变成&&

#SQL-175. Combine Two Tables

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

#SQL-183. Customers Who Never Order

select C.Name as Customers 
from Customers C left join Orders O
on C.Id=O.CustomerId
where CustomerId is NUll;

#也可使用not in
select Name as Customers 
from Customers C
where  C.Id not in
(select CustomerId from Orders);

#SQL-197. Rising Temperature


Select a.Id from
Weather a join Weather b 
where a.Temperature>b.Temperature and To_Days(a.Date)-To_Days(b.Date)=1;

Select a.Id from
Weather a join Weather b 
where a.Temperature>b.Temperature and datediff(a.Date,b.Date)=1;

#SQL- 176. Second Highest Salary

select Max(Salary) as SecondHighestSalary
from Employee
where Salary < (select Max(Salary) from  Employee);

#SQL- 196. Delete Duplicate Emails
delete a
from Person a , Person b  
where a.Email=b.Email and a.Id>b.Id;

#SQL-180. Consecutive Numbers

SELECT DISTINCT L1.Num as ConsecutiveNums
FROM Logs L1, Logs L2, Logs L3
WHERE (L1.Id = L2.Id + 1 AND L1.Num = L2.Num) AND
  (L1.Id = L3.Id + 2 AND L1.Num = L3.Num)

#SQL- 178. Rank Scores

SELECT Scores.Score, COUNT(Ranking.Score) AS RANK  
  FROM Scores  
     , (  
       SELECT DISTINCT Score  
         FROM Scores  
       ) Ranking  
--等于是分组计数 每个分组中方的都是比自己大或相等的所有distinct元素  
 WHERE Scores.Score <= Ranking.Score  
 --分组  
 GROUP BY Scores.Id, Scores.Score   
 --组id排序 降序  
 ORDER BY Scores.Score DESC;   

SELECT Score, Rank   
FROM(  
  SELECT    Score,  
--每一次都每一行都进行判断和计算  
--2 当值为Score,返回0, 否则返回1 也就是不重复的自增 重复的不变  
            @curRank := @curRank + IF(@prevScore = Score, 0, 1) AS Rank,  
            @prevScore := Score  
--1 初始变量值设置为0  
  FROM      Scores s, (SELECT @curRank := 0) r, (SELECT @prevScore := NULL) p  
--3 降序排列  
  ORDER BY  Score DESC  
) t;  

参考：

http://blog.csdn.net/u013458516/article/details/49744291

http://my.oschina.net/Tsybius2014/blog/494823