2018-09-29

交换工资.png

代码：
update salary set=（case when sex='f' then 'm' else 'f' end);

第二高的薪水.png

select Max(t.Salary) SecondHighestSalary from Employee t where t.Salary<(select Max(b.Salary) from Employee b);

上升的温度.png

select W1.id from Weather W1,Weather W2 where W1.Temperature>W2.Temperature
and TO_DAYS(W1.RecordDate)=TO_DAYS(W2.RecordDate)+1

分数排序.png
select t.Score,(select count(distinct t2.Score) from Scores t2 where t2.Score>=t.Score) Rank
from Scores t order by Score desc; 第n高的薪水.png

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct Salary from Employee order by Salary desc limit N,1
);
END select distinct Salary from Employee order by Salary desc limit N,1
);
END

换座位.png
select (case
when mod(id,2)!=0 and id!=counts then id+1
when mod(id,2)!=0 and id=counts then id
else id-1 end
) id,student
from seat,(select count(*) counts from seat) seat_counts
order by id asc 体育馆的人流量.png

select distinct t1.*
from stadium t1,stadium t2,stadium t3
where t1.people>=100 and t2.people>=100 and t3.people>=100
and ((t1.id+1=t2.id and t2.id+1=t3.id and t1.id+2=t3.id )
or (t2.id+1=t3.id and t3.id+1=t1.id and t2.id+2=t1.id)
or(t3.id+1=t1.id and t1.id+1=t2.id and t3.id+2=t2.id))
order by t1.id

部门工资最高的员工.png

select t2.Name Department, t1.Name Employee, t1.Salary
from Employee t1, Department t2
where t1.DepartmentId = t2.Id
and t1.Salary in (select max(Salary)
from Employee, Department
where Employee.DepartmentId = Department.id
group by Employee.DepartmentId)
order by t2.id

删除重复的邮箱.png

select * from Person t1 where t1.id not in(
select min(id) id from person group by Email)

从不订购的用户.png

select t1.name Customers
from Customers t1
where t1.id not in (select CustomerId id from Orders)

连续出现的数.png

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

组合两个表.png

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

超过5名学生的课.png

select distinct t.class from courses t group by t.class having count(distinct t.student)>=5