175.组合两个表
总结:连接两个表用 (表a) join (表b) on 匹配字段
select p.FirstName, p.LastName, a.City, a.State
from Person as p left join Address as a
on p.PersonId = a.PersonId
176.第二高的薪水
总结:offset x 表示省略前x个值;if null(A ,null)表示如果第一项为空,返回null
select ifnull(
(select distinct Salary from Employee #一定要加distinct,
order by Salary desc #先执行select,在执行Order by
limit 1 offset 1),null) as Second HighestSalary
177.第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
IF N < 0 THEN
RETURN NULL;
ELSE
RETURN (
SELECT IFNULL (
(
SELECT
DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N offset 1
),NULL
) AS getNthHighestSalary
);
END IF;
END
178.分数排名(200421)
总结:dense_rank() over (order by …) #重复部分不占名次
rank() over (order by …) #重复部分占名次
row_number() over (order by …) #重复部分都会计名次
select score,
dense_rank() over(order by score desc) as rank
from Scores
180.连续出现的数字(200421)
总结:将表自联,要求id相差1,数字相等
select distinct a.Num as ConsecutiveNums #要用distinct排除相同数字
from Logs as a, Logs as b, Logs as c
where b.id-a.id=1
and c.id-b.id=1
and b.num=a.num
and c.num=b.num
181.超过经理收入的员工
总结:1.连接两个表时,可以用join on +查找字段;也可以用 表a,表b where 限制查询条件(效率更高)
2.对应字段时将两个表连接起来
select a.Name as Employee
from Employee as a, Employee as b #自动进行笛卡尔连接-表a每行都和表b所有行相连接
where a.ManagerId=b.Id
and a.Salary>b.Salary
182.查找重复的电子邮箱
select Email
from Person
group by Email
having count(Email)>1
183.从不订购的客户
总结:两个select 嵌套,选出第二张表中没有的名单
select c.Name as Customers
from Customers as c
where c.Id not in
(select CustomerId from orders)
或者按下面的方式:将表进行做链接,筛选出null的行
select c.Name as Customers
from Customers as c left join Orders as o
on c.Id=o.Id
where o.CustomerId is null
以上为公式应用和连接后的结果
184.部门最高的工资
总结:首先找出Employee中工资最高的行,再和Deaprtment匹配
select d.Name as Department, e.Name as Employee, e.Salary
from Employee as e, Department as d
where e.DeaprtmentId=d.Id
and (e.DepartmentId, e.salary) in
(select DepartmentId, max(Salary)
from Employee
group by Employee.DepartmentId)
196. 删除重复的电子邮箱
总结:1.distinct 只能返回distinct指定的字段,例如,通过distinct name得不到之后的备注列;
2. distinct必须放在开头,当用distinct筛选两列,如distinct name sex, 只要有一列不同就返回。
3. 使用group by,min 找出符合条件的id,再用not in删除
delete from Person
where id not in
(select temp.id
from ((select min(id) as id
from Person
group by email) as temp)) #as 表明之前的部分一定要加括号/必须用到临时表
197.上升的温度
总结:datediff(startdate,enddate)
select a.Id
from Weather as a join Weather as b
on datediff(a.RecordDate, b.RecordDate)=1 #教程上必须要加第一个参数,次数加上就报错
where a.Temperature>b.Temperature
262.行程和用户(200422)
总结:找不出哪里有错
select t.Request_at as Day, round(sum(case when t.Status != 'completed' then 1 end)/count(t.status),2)
as Cancellation_rate
from trips as t
left join Users as a on t.Client_Id=a.Users_Id
left join Users as b on t.Driver_Id=b.Users_Id
where a.Banned='No' and
b.Banned= 'No'
group by t.Request_at
595.大的国家
select name, population, area
from world
where area>3000000
or population>25000000
596.超过5名学生选的课
总结:having相当于where,也是限制条件,出现聚合函数 count、avg等只能用having,并且放在group by 后面
select class
from courses
group by class
having count(distinct student)>=5
620.有趣的电影
总结:取余数用%,不是答案中的mod
select *
from cinema
where id % 2 =1
and description != 'boring'
order by rating desc
626.换座位(200421)
总结:1. 思路:将奇数id-1.偶数id+1
2. case when … then…
else…
select (case
when id%2 = 1 and id = (select count(*) from seat) then id #奇数id,并且是最后一位,id保持不变
when id%2 = 1 then id+1 #不满足条件一时,才判断此条件。奇数id,并且不是最后一位
else id-1
end) as id, student #select id,student
from seat
order by id asc
627.交换工资
总结:1.update ‘表a’ set 替换后的值
2.case函数:case ’列名‘ when…then
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;