公众号merlinsea
题目1、组合两张表
select p.firstname,p.lastname,a.city,a.state from person p left join address a on p.personid = a.personid;
题目2、第二高的薪水
思路1、 通过group by可以将去除重复的薪水,再进行order by可以将去重后的结果进行排序 select salary SecondHighestSalary from employee group by salary order by salary desc limit 1,1 思路2、 1、先查询出最大的薪水 2、过滤出小于最大薪水的所有数据 3、在过滤出的数据中找最大的数据 如果没有最大薪水,max()会返回null select max(salary) SecondHighestSalary from employee where salary < (select max(salary) from employee)
题目3、第N高的薪水
# 执行limit N,M 的时候,如果没有N条记录,则会返回结果为null # limit n,m 表示跳过n条记录,从第n+1条记录开始连续取m条记录 # group by 会根据salary进行去重,去重后的结果根据salary进行降序排列 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN set N = N-1; RETURN ( # Write your MySQL query statement below. select salary from (select salary from employee group by salary order by salary desc) as tmp limit N,1 ); END
题目4、分数排名
# count(distinct(s2.score)) 会先去重后再计算数量 select s1.score,count(distinct(s2.score)) `rank` from scores s1,scores s2 where s1.score <= s2.score group by s1.id order by `rank` select s1.id id1 , s1.score score1 , s2.id id2 , s2.score score2 from scores s1,scores s2 where s1.score <= s2.score # 以s1的视角来看,score2 都是比 score1更大的值,因此这里按score1进行分组,每一个组的数量就是score1的是第几大的元素 # 但score1存在重复的,因此应该以id1进行分组即可 # | id1 | score1 | id2 | score2 | # | --- | ------ | --- | ------ | # | 1 | 3.5 | 1 | 3.5 | # | 6 | 3.65 | 2 | 3.65 | # | 2 | 3.65 | 2 | 3.65 | # | 1 | 3.5 | 2 | 3.65 | # | 6 | 3.65 | 3 | 4 | # | 5 | 4 | 3 | 4 | # | 4 | 3.85 | 3 | 4 | # | 3 | 4 | 3 | 4 | # | 2 | 3.65 | 3 | 4 | # | 1 | 3.5 | 3 | 4 | # | 6 | 3.65 | 4 | 3.85 | # | 4 | 3.85 | 4 | 3.85 | # | 2 | 3.65 | 4 | 3.85 | # | 1 | 3.5 | 4 | 3.85 | # | 6 | 3.65 | 5 | 4 | # | 5 | 4 | 5 | 4 | # | 4 | 3.85 | 5 | 4 | # | 3 | 4 | 5 | 4 | # | 2 | 3.65 | 5 | 4 | # | 1 | 3.5 | 5 | 4 | # | 6 | 3.65 | 6 | 3.65 | # | 2 | 3.65 | 6 | 3.65 | # | 1 | 3.5 | 6 | 3.65 |
题目5、超过经理收入的员工
# 把一张表作为员工表,一张表作为经理表,连接条件是员工表的managerid = 经理表的id select e.name Employee from employee e left join employee m on e.managerid = m.id where e.salary > m.salary
题目6、查找重复的电子邮箱
# 通过group by 进行聚合后,用having来统计每一个分组的条数 select email as Email from person group by email having count(email) > 1
题目7、从不订购的客户
#以customers作为主表来左连接orders表,如果orders表在customers表中不存在则orders的记录以空出现 #因此如果orders为空则说明这个用户不买东西 select c.name Customers from customers c left join orders o on c.id = o.customerid where o.customerid is null
题目8、部门工资最高的员工
#按部门分组找到组内的最高工资 #找到等于这个最高工资的员工 #关联部门名称表 #输出结果 select d.name Department,e.name Employee,e.salary Salary from employee e,department d,( # 按部门分组找到每个部门中的最高工资 select max(salary) max_salary,departmentid dept_id from employee group by departmentid) tmp where e.departmentid = d.id and e.salary = tmp.max_salary and e.departmentid = tmp.dept_id
题目9、部门工资前三高的所有员工
#对于某个分组内取前几名的问题: # 1、表和自身相连,根据分组的唯一标识 + 排名字段 进行关联【on条件】 ---》 可以得到每个分组比当前数据高的所有记录 # 2、根据当前数据的唯一标识id进行分组 【group by】 ---》 分组后可以直接做count统计记录 # 3、过滤出分组后符合排名的记录 【having】 ---》 得到结果 select d.name Department,e.name Employee,e.salary Salary from employee e left join department d on e.departmentid = d.id, (select e1.id # 这里的left join的条件是部门相同且工资比e1高的进行关联 from employee e1 left join employee e2 on e1.departmentid = e2.departmentid and e1.salary < e2.salary group by e1.id having count(distinct e2.salary) <= 2 # 去重后统计 ) tmp where e.id = tmp.id
题目10、删除重复的电子邮箱
1、先找到每个分组内id最小的记录 2、将不属于第一步查出的id的记录进行删除 delete from person where id not in( select * from( select min(id) from person group by email ) as min_id )
题目11、上升的温度
select w1.id from weather w1 left join weather w2 on w1.recorddate = date_add(w2.recorddate,interval 1 day) where w1.temperature > w2.temperature;
题目12、员工奖金
# select e.name,b.bonus # from employee e left join bonus b on e.empid = b.empid # where b.bonus<1000 # 这里b.bonus<1000是要求是不为空的同时小于1000 # | name | bonus | # | ------ | ----- | # | Brad | null | # | John | null | # | Dan | 500 | # | Thomas | 2000 | select e.name,b.bonus from employee e left join bonus b on e.empid = b.empid where b.bonus<1000 or b.bonus is null
题目13、订单最多的客户
1、先查出每个客户下了多少订单 2、根据订单的数量按降序排布 3、通过limit取第一个元素【即下单最多的客户】 select customer_number from( select customer_number,count(*) cnt from orders group by customer_number ) tmp order by cnt desc limit 0,1
题目14、2016年的投资
# group by lat,lon表示只有lat和lon都一样的时候才是一个分组,当group by多个维度的时候,只有每个维度都相同才会归为一个分组 select lat,lon,count(*) from insurance group by lat,lon # | lat | lon | count(*) | # | --- | --- | -------- | # | 10 | 10 | 1 | # | 20 | 20 | 2 | # | 40 | 40 | 1 | #由于题目中需要找城市必须与其他投保人都不同的分组,因此这些符合条件的分组其组内只有一条记录,这时候可以取pid【不会产生歧义】 select round(sum(TIV_2016),2) tiv_2016 from insurance where pid in( select pid from insurance group by lat,lon having count(*)=1 ) and TIV_2015 in( select TIV_2015 from insurance group by TIV_2015 having count(*)>1 )
题目15、连续出现的数字
# 通过left join把两个表链接起来,链接的条件是数字相等且id大1 select distinct(l1.num) ConsecutiveNums from logs l1 left join logs l2 on l1.num = l2.num and l1.id +1 = l2.id left join logs l3 on l1.num = l3.num and l2.id +1 = l3.id where l2.id is not null and l3.id is not null
题目16、好友申请 II :谁有最多的好友
# 注意,在进行group by以后每个分组内只有一条记录,后续进行order by不会影响group by的结果。 SELECT id,SUM(num) AS num FROM ( # 用户发出好友请求的数量 SELECT requester_id AS id,COUNT(accepter_id ) AS num FROM RequestAccepted GROUP BY requester_id UNION ALL # 用户接收好友请求的数量 SELECT accepter_id AS id,COUNT(requester_id ) AS num FROM RequestAccepted GROUP BY accepter_id ) AS o GROUP BY id ORDER BY num DESC LIMIT 1
题目17、 销售员
select name from salesperson where sales_id not in( # 找到与这个公司有关的销售人员的id select sales_id from orders where com_id in( # 通过公司名找到公司的id select com_id from company where name = 'RED') )
题目18、树节点
select t1.id id, case when t1.p_id is null then 'Root' when t2.id is null then 'Leaf' else 'Inner' end type from tree t1 left join tree t2 on t1.id = t2.p_id group by t1.id #去重 select t1.*,t2.* from tree t1 left join tree t2 on t1.id = t2.p_id 当前节点 父节点 子节点 # | id | p_id | id | p_id | # | -- | ---- | ---- | ---- | # | 1 | null | 3 | 1 | # | 1 | null | 2 | 1 | # | 2 | 1 | 5 | 2 | # | 2 | 1 | 4 | 2 | # | 3 | 1 | null | null | # | 4 | 2 | null | null | # | 5 | 2 | null | null |
题目19、只出现一次的最大数字
# 当子查询的结果为空的时候,max过滤出的结果也是空 select max(num) num from( select num from mynumbers group by num having count(*)=1 ) tmp # 当having过滤出的结果为空的时候,什么也不会输出 select num from mynumbers group by num having count(*)=1 order by num desc limit 1
题目20、有趣的电影
select * from cinema where description != 'boring' and id%2=1 order by rating desc
题目21、产品销售分析 III
select sales.product_id,sales.year first_year,sales.quantity,sales.price from sales,( # 找出每个产品的第一年售卖的数据 select product_id,min(year) year from sales group by product_id) tmp where sales.product_id = tmp.product_id and sales.year = tmp.year
题目22、项目员工 I
# 当有的员工没有项目做的时候,通过project_id进行分组会导致有的记录会被分到null的组 select pro.project_id,avg(emp.experience_years) average_years from employee emp left join project pro on emp.employee_id = pro.employee_id group by project_id # | project_id | average_years | # | ---------- | ------------- | # | 1 | 3 | # | null | 2 | # | 2 | 1.5 | select pro.project_id,round(avg(emp.experience_years),2) average_years from project pro left join employee emp on emp.employee_id = pro.employee_id group by project_id
题目23、销售分析III
# 这只是查出了在2019春季卖出过产品的记录,但【不是只在】2019年春季卖出商品的记录 select pro.product_id,pro.product_name from product pro,sales where sales.sale_date>='2019-01-01' and sales.sale_date<='2019-03-31' and pro.product_id = sales.product_id # 根据最值来查询出【只在】2019年春季卖出过的产品 select pro.product_id,pro.product_name from product pro,( select product_id,min(sale_date) mindate,max(sale_date) maxdate from sales group by product_id having mindate>='2019-01-01' and maxdate<='2019-03-31') tmp where pro.product_id=tmp.product_id