#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