简单

595.大的国家

select name, population, area from World where (area > 3000000 or population>25000000)

596.超过5名学生的课

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

196.删除重复的邮箱

delete p1 from Person p1, Person p2 where p1.Email = p2.Email and p1.Id > p2.Id

181.超过经理收入的员工

select e1.Name as Employee from Employee e1 inner join Employee e2 on e1.MangerId = e2.Id and e1.Salary > e2.Salary

620.有趣的电影

select id,movie,description,rating from cinema where mod(id,2)=1 and description <> 'boring' order by rating desc

select id,movie,description,rating from cinema where mod(id,2)=1 and description <> 'boring' order by rating desc

197.上升的温度

select w2.Id from Weather w1 join Weather w2 on date_add(w1.RecordDate,INTERVAL 1 day) = w2.RecordDate and w1.Temperature < w2.Temperature

176.第二高的薪水

select MAX(e.Salary) as SecondHighestSalary from Employee e where e.Salary < (select max(e1.Salary) from Employee e1 )

627.交换工资

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

中等

180.连续出现的数字

select distinct l.Num as ConsecutiveNums from Logs l,Logs l1,Logs l2 where l.Id = l1.Id -1 and l.Id = l2.Id - 2 and l.Num = l1.Num and l.Num = l2.Num

184.部门工资最高的员工

select d.Name as Department, e.Name as Employee, e.Salary from Employee e inner join Department d on e.DepartmentId = d.Id where e.Salary >= (select max(Salary) from Employee e1 where e1.DepartmentId = e.DepartmentId group by DepartmentId)

626.换座位

SELECT (CASE WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id WHEN MOD(id,2) = 1 THEN id+1 ElSE id-1 END) AS id, student FROM seat ORDER BY id;

177.第N高薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( # Write your MySQL query statement below. select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary ) ); END

178.分数排名

SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank FROM Scores s ORDER BY Score DESC ;

困难

185.部门工资前三高

select d.name as Department , e.name as Employee, e.Salary as Salary from Employee as e inner join Department as d on e.DepartmentId = d.Id where ( select count(distinct salary) from Employee e1 where e1.Salary> e.Salary and e1.DepartmentId = e.DepartmentId )<3 order by e.DepartmentId,e.Salary desc

601.体育馆的人流量

select distinct a.* from stadium a,stadium b,stadium c where a.people>=100 and b.people>=100 and c.people>=100 and ( (a.id = b.id-1 and b.id = c.id -1) or (a.id = b.id-1 and a.id = c.id +1) or (a.id = b.id+1 and b.id = c.id +1) ) order by a.id