sql题目集锦

简介: sql题目集锦

公众号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


相关文章
|
SQL
SQL 训练题目
解题思路:要查询的是两个课程的成绩,而且还要显示学生的信息。所以需要用到两张表,SC,Student 这两张表。
117 0
|
6月前
|
SQL 数据库
数据库Sql题目 : 不用id,每隔10行取一条数据
数据库Sql题目 : 不用id,每隔10行取一条数据
|
SQL Oracle 关系型数据库
实际开发中我印象最深的一道SQL题目
SQL不仅是一个工具,更是一种思维方式,它可以帮助我们发掘数据中的潜在价值,为业务决策提供有效的支持。在软件开发过程中,数据库也是很重要的一环。
112 1
|
SQL 数据库
阿里云瑶池数据库SQL挑战令题目三
阿里云瑶池数据库SQL挑战令题目三
109 1
|
SQL 存储 关系型数据库
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(2)
|
存储 SQL 缓存
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
数据库语句执行过程理解,存储引擎的学习,字符集的理解,加各种sql数据类型理解介绍加之SQL各种数据查询分析和题目专练(1)
多表查询 SQL21用户题目回答情况(三种写法)
多表查询 SQL21用户题目回答情况(三种写法)
160 0
多表查询 SQL21用户题目回答情况(三种写法)
|
SQL MySQL 关系型数据库
MySQL关于SQL语句的一些题目
MySQL关于SQL语句的一些题目 1、用一条SQL语句查询出每门课都大于80的学生的姓名 (1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80 (2)SELECT DISTINCT NAME FROM score WH.
1653 0